> If I had a single table targ to insert into I would do an > > INSERT INTO targ SELECT thiscol, thatcol, theothercol FROM FOO. > > The problem is that I have tables targ1, targ2, targn to insert things into and a > nice stored procedure myproc which does the insertion into all 3 tables - > problem is that I dont see how I can effectively do > > INSERT INTO myproc SELECT thiscol, thatcol, theothercol FROM FOO. > > The only way I can work out how to do this is with another stored procedure > which allows me to do: > > FOR rec IN SELECT thiscol, thatcol, theothercol > FROM FOO > LOOP > PERFORM myproc(rec.thiscol, rec.thatcol, rec.theothercol); > END LOOP; > > But is there a way to do this just in SQL only without resorting to plpgsql or a > language like C/Java? > > thanks > SELECT myproc(thiscol, thatcol, theothercol) FROM FOO If you want to use (and explode) the return value of "myproc" you will have to do: WITH func_exec AS ( SELECT myproc(thiscol, thatcol, theothercol) FROM FOO ) SELECT (func_exec.myproc).* FROM func_exec; If you do: SELECT myproc(thiscol, thatcol, theothercol).* FROM FOO The "myproc" function will be executed one time for every output *column* defined; which is likely to cause the statement to fail (since you'd be inserting the same exact data multiple times). David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
