PostgreSQL function execute query

Question

I want to run a SQL query if a condition is met, but I get the following error:

ERROR: a separate $ chain is unfinished in or near «$func$

my SQL query is:

CREATE OR REPLACE FUNCTION myfunc()
RETURNS TABLE(dateticket date, timeticket time, userid integer, my_all bigint) AS
    $func$
    BEGIN
            IF (SELECT COUNT(DISTINCT(dateticket)) from tickets) = 1 THEN
               RETURN QUERY EXECUTE 'select t.*
               from (select distinct on (userid) dateticket, timeticket, userid,
               count(*) over (partition by userid) as my_all
               from tickets t
               order by userid, dateticket, timeticket) t
               order by my_all, dateticket, timeticket';
            ELSE
               RETURN QUERY EXECUTE 'select t.*
               from (select distinct on (userid) dateticket, timeticket, userid,
               count(*) over (partition by userid) as my_all
               from tickets t
               order by userid, dateticket, timeticket) t
               order by my_all DESC, dateticket DESC, timeticket DESC';
            END IF;
    END;
    $$ LANGUAGE plpgsql;

Answer 1

The error is actually not about the condition or anything in the function itself, but the syntax of the function creation. You start the function definition with $func$ and end it with $$. This will not work.

Change the $func$ to $$ to fix the syntax.

Answer 2

You have two fault in the function one is already answered the another one that the RETURN table's column is same as the columns name of the select query used inside the fucntion this will cause

ERROR: column reference "dateticket" is ambiguous LINE 1: (SELECT COUNT(DISTINCT(dateticket)) from tickets) = 1 ^ DETAIL: It could refer to either a PL/pgSQL variable or a table column. QUERY: (SELECT COUNT(DISTINCT(dateticket)) from tickets) = 1 CONTEXT: PL/pgSQL function myfunc() line 3 at IF ********** Error **********

so you need to modify your function like below

CREATE OR REPLACE FUNCTION myfunc()
RETURNS TABLE(datet_icket date, time_ticket time, user_id integer, myall bigint) AS
    $$
 BEGIN
     IF (SELECT COUNT(DISTINCT(dateticket)) from tickets) = 1 THEN
         RETURN QUERY EXECUTE 'select t.*
         from (select distinct on (userid) dateticket, timeticket, userid,
         count(*) over (partition by userid) as my_all
         from tickets t
         order by userid, dateticket, timeticket) t
         order by my_all, dateticket, timeticket';
     ELSE
          RETURN QUERY EXECUTE 'select t.*
          from (select distinct on (userid) dateticket, timeticket, userid,
          count(*) over (partition by userid) as my_all
          from tickets t
          order by userid, dateticket, timeticket) t
          order by my_all DESC, dateticket DESC, timeticket DESC';
          END IF;
    END;
    $$ LANGUAGE plpgsql;