Skip site navigation (1) Skip section navigation (2)

Re: functions

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: functions
Date: 2010-03-28 02:44:14
Message-ID: homfpu$vfk$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-novice
On 2010-03-27, Kent Scott <kscott(at)logicalsi(dot)com> wrote:
> I am having a hard time coming from MS SQL in terms of creating 
> functions. Minor things keep you from being able to create functions 
> that are not an issue in MS SQL so I need help in understanding why they 
> are an issue so that I can ultimately create the functions that I need 
> to. I can create and run the following function fine :
>
> CREATE FUNCTION listSales(dt date,movie text) RETURNS SETOF ticket AS
> $$
> BEGIN
>     select * from ticket where date = $1 and mov_num = $2;
> END;
> $$
> LANGUAGE SQL;

doesn't work for me, I get a syntax error at select.

If I remove BEGIN and END;  it works.


> however, the following will not create :
> CREATE FUNCTION listSales(dt date,movie text) RETURNS SETOF ticket AS
> $$
> declare
>     r1 int;
> BEGIN
>     select * from ticket where date = $1 and mov_num = $2;
> END;
> $$
> LANGUAGE SQL;
>
> I get a syntax error on r1 int and I have no idea why. Am I not allowed 
> to declare variables if they are not used?

PLPGSQL id a different language to SQL.
DECLARE, BEGIN, and END; ( used in this way) are PLPGSQL not SQL.
SELECT is not the way to return values from a PLPGSQL function.

The following is roughly equivalent to the corrected version of your SQL
function above.

CREATE OR REPLACE FUNCTION listSales(dt date,movie text) RETURNS SETOF ticket AS
$$
declare
r1 integer;
t ticket;
BEGIN
 for t IN select * from ticket where date = $1 and mov_num = $2
 loop
   return next t;
 end loop;
 return;
END;
$$
LANGUAGE PLPGSQL;

In response to

  • functions at 2010-03-27 21:34:53 from Kent Scott

pgsql-novice by date

Next:From: Vitali XevetDate: 2010-03-28 04:30:25
Subject: Clustering/fail-over solution
Previous:From: Kent ScottDate: 2010-03-27 21:34:53
Subject: functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group