Re: PL/PGSQL Record type question

On 5/11/07, Robins <tharakan@gmail.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