PostgreSQL function execute query

Question 1

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;

Question 2

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

Question 3

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.

Question 4

@Ivan It should be when you execute the function

Question 5

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;

Sami Kuhmonen 31.4k9 gold badges67 silver badges81 bronze badges · Accepted Answer · 2015-06-20 05:11:30Z

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.

Share

Improve this answer

answered Jun 20, 2015 at 5:11

Sami Kuhmonen's user avatar

Sami Kuhmonen

31.4k9 gold badges67 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sami Kuhmonen

Sami Kuhmonen Over a year ago

@Ivan It should be when you execute the function

2015年06月20日T05:17:05.07Z+00:00

CollectivesTM on Stack Overflow

PostgreSQL function execute query

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Hot Network Questions

CollectivesTM on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Post as a guest

Related