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;

• Sami Kuhmonen: You start the function with $func$ but try to end it with $$. These must match

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.

• Sami Kuhmonen: @Ivan It should be when you execute the function

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;