DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=BD6ntBQmwPKzdShzZGZCj7KZ/0VC/YlViTmhxIezZFHfu3Y01Ia/lgWopanmYNfnOU7nx2rloXBNBYKO+wQNM+6Gp4+KITwP+P0XooU4kvWwFdz+P6/d9tHNhJHA2fPQnuPQr3oJCwVEwFenuubhhPtzTecbx3OiHuj1N7mJsoU=
Message-ID: <1f30b80c0705111119v7b0520ebgbda9ae7e07259bc5@mail.gmail.com>
Date: Fri, 11 May 2007 23:19:52 +0500
From: imad <immaad@gmail.com>
To: robins@pobox.com
Subject: Re: PL/PGSQL Record type question
Cc: "=?ISO-8859-1?Q?G=E1briel_=C1kos?=" <akos.gabriel@i-logic.hu>,
	pgsql-sql@postgresql.org
In-Reply-To: <36af4bed0705111039r561f1c3bod9183535ed86763@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20070511160037.1c09160e@gabriel-laptop>
	<36af4bed0705111039r561f1c3bod9183535ed86763@mail.gmail.com>

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