Re: PERFORM not working properly, please help..

[wilczarz1@xxxxx]

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.stehule@xxxxxxxxx> napisał(a): 
 > 2010/2/19  <wilczarz1@xxxxx>:
 > > 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.stehule@xxxxxxxxx> napisał(a):
 > > Â > Hello
 > > Â >
 > > Â > 2010/2/18 Â <wilczarz1@xxxxx>:
 > > Â > > 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..
 > > Â > >
 > >
 > >


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general