current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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;
share|improve this question
2  
You start the function with $func$ but try to end it with $$. These must match –  Sami Kuhmonen yesterday

2 Answers 2

up vote 1 down vote accepted

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
    
ready... but the SQL query is not executed. –  Ivan yesterday
    
@Ivan It should be when you execute the function –  Sami Kuhmonen yesterday
up vote 0 down vote

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.