Re: plpgsql function returning SETOF

Hello Andreas,
hello novices and experts,

thanks again for your immediate help!

Andreas Kretschmer <akretschmer@spamfence.net> writes:

> Johannes Brügmann <johannes@jottbee.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