Oki, I found a way...
begin;
select trykkstatus(1,'refcurs'); (I have added one parameter to know the cursor name)
fetch all from refcurs;
commit;
But this returns two rowsets, first one for the select and then one for the fetch,
but how do I get rid of the row that is returned by the select?
BTJ
Bjørn T Johansen wrote:
> Yes, of course....
>
> But this function does not do what I need it to do... I want x rows returned, but
> instead I just get a stringname...
> Either how do I use this name or how do I return x rows?
>
>
> BTJ
>
> Gnanavel S wrote:
>
>>'IF' block is not ended.
>>
>>On 9/15/05, *Bjørn T Johansen* <btj@havleik.no <mailto:btj@havleik.no>>
>>wrote:
>>
>> I am trying to write a function that returns x rows, where x >= 0
>> and this is what I
>> have come up with...:
>>
>> CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS
>> REFCURSOR AS '
>> declare
>> orderID ordrenew.id%TYPE;
>> cur REFCURSOR;
>> begin
>> select id into orderID from ordrenew where now() between trykkstart
>> and produsert and
>> presseid = $1 limit 1;
>> if not found then
>> raise exception ''No rows'';
>> open cur for ((select 1 as colid, id, trykkstart, produsert,
>> presseid from ordrenew
>> where produsert < (select trykkstart from ordrenew where id=orderID)
>> order by produsert desc limit 1)
>> union
>> (select 2 as colid, id, trykkstart, produsert, presseid from
>> ordrenew where now()
>> between trykkstart and produsert and presseid = 1 limit 1)
>> union
>> (select 3 as colid, id, trykkstart, produsert, presseid from
>> ordrenew where
>> trykkstart > (select produsert from ordrenew where id=orderID) order
>> by trykkstart
>> limit 1) order by colid);
>> return(cur);
>> END;
>> ' LANGUAGE 'plpgsql';
>>
>>
>> But this just gives me the following error:
>>
>> syntax error at or near ";" at character 851
>>
>> And I can't find anything wrong near any ; ....?
>> Also, does this function do what I expect it to do?
>> And instead of rasing an error when no rows is found, can I return
>> an "empty" cursor
>> instead?
>>
>>
>> Regards,
>>
>> BTJ
>>
>> --
>> -----------------------------------------------------------------------------------------------
>>
>> Bjørn T Johansen
>>
>> btj@havleik.no <mailto:btj@havleik.no>
>> -----------------------------------------------------------------------------------------------
>> Someone wrote:
>> "I understand that if you play a Windows CD backwards you hear
>> strange Satanic messages"
>> To which someone replied:
>> "It's even worse than that; play it forwards and it installs Windows"
>> -----------------------------------------------------------------------------------------------
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>
>>
>>
>>
>>--
>>with regards,
>>S.Gnanavel
>>Satyam Computer Services Ltd.
>
>