From: | wilczarz1(at)op(dot)pl |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PERFORM not working properly, please help.. |
Date: | 2010-02-19 10:32:05 |
Message-ID: | Q18103027-dce2336ee7fdba023073b73ad13d2f65@pmq1.m5r2.onet.test.onet.pl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I suppose some workaround would be to introduce temporary cursor:
CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
declare _tmp record;
begin
select * from A1() as dummy ( x double precision ) into _tmp;
end;
$BODY$ LANGUAGE 'plpgsql';
But I'm not sure if this is more effiecent than A3 returning the set. Thanks for replies!
"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> napisał(a):
> 2010/2/19 <wilczarz1(at)op(dot)pl>:
> > Hi Pavel, thanks for reply. Your solution:
> >
> > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> > begin
> > Â return query select * from A1();
> > Â return;
> > end;
> > $BODY$ LANGUAGE 'plpgsql';
> >
> > generates error "cannot use RETURN QUERY in a non-SETOF function" because A3 returns VOID.
>
> problem is in A3, cannot be void.
>
> PostgreSQL has only function. It hasn't "procedures" where you can
> execute unbinded queries. So if you can take any result from any
> rutine, you have to take it explicitly. VOID in pg means, there are no
> any interesting result, really no any interesting result. It can be
> problem, when you know MySQL procedures or MSSQL procedures. You have
> to forgot on procedures with returning recordset or multirecordset as
> secondary effect.
>
> regards
> Pavel Stehule
>
> >
> >
> > "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> napisał(a):
> > Â > Hello
> > Â >
> > Â > 2010/2/18 Â <wilczarz1(at)op(dot)pl>:
> > Â > > I have a function A1 that returns setof records, and I use it in two ways:
> > Â > > 1) from function A2, where I need results from A1
> > Â > > 2) from function A3, where I don't need these results, all I need is to
> > Â > > execute logic from A1
> > Â > >
> > Â > > Here ale very simple versions of my functions:
> > Â > >
> > Â > > CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$
> > Â > > begin
> >  > > � -- some logic here
> >  > > � return query select col from tab;
> > Â > > end;
> > Â > > $BODY$ LANGUAGE 'plpgsql';
> > Â > >
> > Â > > CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$
> > Â > > begin
> >  > > � -- some logic here
> >  > > � return query select * from A1() as dummy ( x double precision);
> > Â > > end;
> > Â > > $BODY$ LANGUAGE 'plpgsql';
> > Â > >
> > Â > > CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> > Â > > begin
> >  > > � perform A1();
> > Â > > end;
> > Â > > $BODY$ LANGUAGE 'plpgsql';
> > Â > >
> > Â > > And here are my function calls:
> > Â > > select * from A1() as(x double precision) --ok
> > Â > > select * from A2() as(x double precision) --ok
> > Â > > select * from A3(); --not ok, argh!
> > Â > >
> > Â >
> > Â > it is correct. Every function has own stack for result. There are not
> > Â > some global stack. Perform just run function and doesn't copy inner
> > Â > result's stack to outer result stack.
> > Â >
> > Â > your A3 function have to be
> > Â > begin
> > Â > Â return query select * from a1
> > Â > Â return;
> > Â > end;
> > Â >
> > Â > like a2 function
> > Â >
> > Â > regards
> > Â > Pavel Stehule
> > Â > > The last one generates error "set-valued function called in context that
> > Â > > cannot accept a set". Why doesn't PERFORM work here? Thanks for help..
> > Â > >
> >
> >
From | Date | Subject | |
---|---|---|---|
Next Message | Florent THOMAS | 2010-02-19 10:52:29 | Re: PERFORM not working properly, please help.. |
Previous Message | Magnus Hagander | 2010-02-19 10:28:03 | Re: How to get the users name from users group? |