Robert James wrote
> I'd like a stored procedure which does something like:
>
> INSERT INTO...
> SELECT... -- This should be returned as multicolumn, multifield - just
> like a table or view
>
> When I run it, though, instead of getting a table, I get one field
> with all the data in it as a compound type. I'd like to return the
> results just like a view.
>
> How do I do that?
>
> Additionally, I'd like to put a:
> DELETE...
> at the end, but still return the SELECT (i.e. what SELECT said before
> the DELETE). Is that possible?
>
> Finally, my preference is to do all this in a SQL stored procedure,
> not PL/pgSQL or PL/anythingelse.
>
> Thanks!
Would help if you provide version information and an example of what you
actually tried.
Anyway,
SELECT function_call(...) --this will result in a single composite-typed
column.
SELECT * FROM function_call(...) -- this gets you a normal "view-like"
output. Make use of CTE/WITH constructs if needed.
WITH func AS ( SELECT function_call(...) FROM ... )
SELECT (func.function_call).* FROM func
If you are going to insist on an arbitrary refusal to use pl/pgsql you are
going to have problems with combining multiple statements in the same
function since the last one execute is the one whose results are returned.
For your first question you use:
INSERT INTO ...
SELECT * FROM ...
RETURNING ...
You can use:
DELETE FROM ... RETURNING ...
to handle the second question.
PostgreSQL has added a "RETURNING" clause to INSERT/UPDATE/DELETE for this
very use-case.
Also, starting with 9.1, you can use these constructs within a CTE/WITH
clause (prior to 9.1 you could only use SELECT).
HTH,
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Return-cols-and-rows-via-stored-procedure-tp5763727p5763732.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.