Hi guys,
First time (I think, certainly recently) posting to this mailing list.
I've been casually using Postgres for a couple of years, but recently am
going to be using it in a professional settings, so I figure it's time
to get serious about learning the right way to write functions/stored
procedures.
In the past year, I've spent a lot of time writing MS SQL Server stored
procedures, which are pretty good as they go, and I'm essentially trying
to port some actual procedures and my skill set at writing those to
Postgres. I'm finding it a bit difficult as there are (of course) some
pretty fundamental differences.
I think I've handled most of the important ones so far, but there's one
that's been bugging me. In MSSQL, I can write a stored procedure that
does something like this:
CREATE PROCEDURE test(
@lookup char(50))
WITH ENCRYPTION AS BEGIN
-- ... a bunch of code to do some lookup, and then ...
SELECT
@Result1 AS Result1,
@Result2 AS Result2,
@Result3 AS Result3,
@Result4 AS Result4
END
GO
and then when I call this procedure, I get a result row (like it came
from a SELECT on a table) which has the columns neatly labeled with
'Result1', 'Result2', etc. Note that these column labels are rather
arbitrary and not necessarily associated with a single table or perhaps
even any existing column in a table.
The question is, how can I best (most easily and elegantly) handle this
in plpgsql? I've spent a few hours researching it and it seems like you
either use a single (or set of) record or composite types. The only way
I could get it to work with records though, was to specify the layout of
the row on the actual call to the plpgsql function, which is
undesirable. And if I use a composite type, I'm going to have to setup
a separate composite type for every special return tuple I might want
out of a function (I do this in a few different places, want to return
perhaps 2-4 *named* columns as results from a function call).
I hope I've made what I'm looking to do clear. Is there any slick way
of handling this that allows the column naming to occur completely
within the function itself and perhaps doesn't require an external table
or composite type definition?
Thanks,
John Lawler
