Re: PL/PGSQL Record type question

On 5/11/07, Robins <tharakan(at)gmail(dot)com> wrote:
> Hi Gabriel,
>
> There are two ways to do this:
> 1. Imad's way (Define the function with the return type as RECORD). Its only
> problem is that while querying from this function, you need to give a proper
> SELECT query or else PG returns an error.

Yeah ... valid point.

>
> e.g. As Imad gives in his example ...
>
> CREATE FUNCTION xyz() RETURNS record AS
> $$
> declare
> abc RECORD;
> begin
> abc := (1, 2);
> return abc;
> end;
> $$
> language plpgsql;
>
> And execute the function in this fashion:
>
> select a, b from xyz() as (a int, b int);
>
> The only problem with this is that if you have 6 elements your select
> statement becomes quite long. Also, in case your function return parameter
> count changes, or its types change, you would need to change the SELECT SQL
> at all the places.
>
> 2. Define a TYPE as John mentioned, and set the function's return type as
> this TYPE. The advantage is that you can always redefine the function and
> the type in case the return parameters are changing and that your select
> statement is a simple SELECT * from fn().

The drawback is that you need to know the complete definition in
advance. In contrast, you can assign any type of row to a RECORD
variable ... much more flexible.

--Imad
www.EnterpriseDB.com