Maybe my french english disallowed me to understand right the question, but I think that this item could help in a way!
|
|
Le vendredi 19 février 2010 à 11:04 +0100, Pavel Stehule a écrit :
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.. > > > > >
