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;

You start the function with $func$ but try to end it with $$. These must match — Sami Kuhmonen
– Sami Kuhmonen, Commented Jun 20, 2015 at 5:09

Sami Kuhmonen · 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.

answered Jun 20, 2015 at 5:11

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 Over a year ago

@Ivan It should be when you execute the function

Vivek S. · Accepted Answer · 2015-06-20 05:44:31Z

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;

Collectives™ on Stack Overflow

PostgreSQL function execute query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related