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

Re: plpgsql function returning SETOF

From: "Johannes Brgmann" <johannes(at)jottbee(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: plpgsql function returning SETOF
Date: 2005-12-21 18:57:15
Message-ID: 5zirtib7n8.fsf@jottbee.net (view raw or flat)
Thread:
Lists: pgsql-novice
Hello Andreas,
hello novices and experts,

thanks again for your immediate help!

Andreas Kretschmer <akretschmer(at)spamfence(dot)net> writes:

> Johannes Brügmann <johannes(at)jottbee(dot)org> schrieb:
>> I always had this in mind:
>> 
>> ,----[ ${doc}/postgresql-8.0.3/html/xfunc-sql.html ]
>> | 31.4.4. SQL Functions Returning Sets
>> |
>> | When an SQL function is declared as returning SETOF sometype, the
>> | function's final SELECT query is executed to completion, and each row
>> | it outputs is returned as an element of the result set. 
>> `----[ end ]
>
> If you write the function in the language 'sql', then this is right.
> But in your function there are things like 'if...', so you need plpgsql.

This is what i tried after understanding it with your help, but i got
a syntax error in the DECLARE part just before timestamptz, so i
thought the type wouldn't be supported by language 'sql'. I didn't
know that the IF control expressions where the bad guys. Maybe I try
it again in language 'sql', because IF should be replacable in what i
want. 

>> But this is SQL isn't it, ;-)? And SQL doesn't support timestamp, does
>> it? (AAaaarrggghhhh...)
>
> ???
> timestamp and timestamptz are valid types in SQL.

yes, i wrote it before i thought about it; sorry.

>> A new problem is now, that i still can't get it to work after all:
>> 
>>       CREATE TYPE feiertag AS (bezeichnung VARCHAR(100), datum TIMESTAMP WITH TIME ZONE);
>>       
>>       CREATE OR REPLACE FUNCTION feiertage(TIMESTAMP WITH TIME ZONE) 
>> ...
>>       END;
>>       $$ LANGUAGE plpgsql;
>> 
>> Where is the bug now?
>
> I can't see a error-message, sorry ;-)

yes, i recognized my mistake already.. The other posting contains the solution.

> Btw.: we have a german mailing list too ;-)

Aah, next time!

Thanks you very much for your patience and the immediate help
Johannes


In response to

pgsql-novice by date

Next:From: Johannes BrgmannDate: 2005-12-21 19:08:56
Subject: Re: plpgsql function returning SETOF
Previous:From: Tom LaneDate: 2005-12-21 18:50:44
Subject: Re: plpgsql function returning SETOF

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