Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 601AC9FB621 for ; Fri, 11 May 2007 14:39:14 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 57495-04 for ; Fri, 11 May 2007 14:39:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.250]) by postgresql.org (Postfix) with ESMTP id A9ABE9FB403 for ; Fri, 11 May 2007 14:39:09 -0300 (ADT) Received: by ag-out-0708.google.com with SMTP id 23so995492agd for ; Fri, 11 May 2007 10:39:08 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Cc3b+myOPUzL7wrrsZkjKwU0GCKDJzsWhXREj4qvoysmyib3jyC4UH/WZHd/hur+yU5xbIzV2WZ5yx4yS2wD6vA7NmvPOe/efX027/bSuqjl7KMQul+x6P7LK5lPTsZxYHzobTr8NhMA5wx9npGPffJ4NbZ2briW6IbCv4hAUEY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=srKtxt5JZlo+YVrZmySS2EhAf7svGONzxRq6SxgZiS8UB6fMYFPtt4h9QRwB4AGTAy/nVThBeye5EI7CZP9Kinx0YMBKWUATpDHWtFQ0xYe+COFX2VBgue8cmxlst3rH81sr++PXJtUEVcFtMugNz31dZ7Bq/QAPB2jnFKWQP00= Received: by 10.90.71.3 with SMTP id t3mr3329797aga.1178905148881; Fri, 11 May 2007 10:39:08 -0700 (PDT) Received: by 10.90.119.10 with HTTP; Fri, 11 May 2007 10:39:08 -0700 (PDT) Message-ID: <36af4bed0705111039r561f1c3bod9183535ed86763@mail.gmail.com> Date: Fri, 11 May 2007 23:09:08 +0530 From: Robins Reply-To: robins@pobox.com To: "=?ISO-8859-1?Q?G=E1briel_=C1kos?=" Subject: Re: PL/PGSQL Record type question Cc: pgsql-sql@postgresql.org In-Reply-To: <20070511160037.1c09160e@gabriel-laptop> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_149290_20072295.1178905148825" References: <20070511160037.1c09160e@gabriel-laptop> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/89 X-Sequence-Number: 28369 ------=_Part_149290_20072295.1178905148825 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Gabriel, There are two ways to do this: 1. Imad's way (Define the function with the return type as RECORD). Its onl= y problem is that while querying from this function, you need to give a prope= r SELECT query or else PG returns an error. e.g. As Imad gives in his example ... CREATE FUNCTION xyz() RETURNS record AS $$ declare abc RECORD; begin abc :=3D (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(). Personally, I have tried both and believe the second way (TYPE) is quite convenient for me. Regards, Robins Tharakan On 5/11/07, G=E1briel =C1kos wrote: > > Hi, > > How should I define a record type (there is no table with this record > type) programmatically in pl/pgsql? > I'd like to return a record with 3 string elements, 2 integers and 1 > date. > > Rgds, > Akos > > > > -- > =DCdv=F6zlettel, > G=E1briel =C1kos > -=3DE-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu =3D- > -=3DTel/fax:+3612367353 |Mobil:+36209278894 =3D- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > --=20 Robins ------=_Part_149290_20072295.1178905148825 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Gabriel,

There are two ways to do this:
1. Imad's way (Def= ine 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 o= r else PG returns an error.

e.g.  As Imad gives in his example ...

CREATE FUNCTION xyz() RETURNS record AS
$$
 d= eclare
 abc RECORD;
 begin
 abc :=3D (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 proble= m with this is that if you have 6 elements your select statement becomes qu= ite long. Also, in case your function return parameter count changes, or it= s 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 retu= rn type as this TYPE. The advantage is that you can always redefine the fun= ction and the type in case the return parameters are changing and that your= select statement is a simple SELECT * from fn().=20

Personally, I have tried both and believe the second way (TYPE) is = quite convenient for me.

Regards,
Robins Tharakan

On 5/11/= 07,=20 G=E1briel =C1kos <akos.gabriel@i-logic.hu> wrote: Hi,

How should I define a record type (there is no table with this r= ecord
type) programmatically in pl/pgsql?
I'd like to return a re= cord with 3 string elements, 2 integers and 1
date.

Rgds,
Akos



--
=DCdv=F6zlettel,
G=E1briel =C1kos
-=3DE-Mai= l :akos.gabriel@i-logic.hu|Web:  http://www.i-logic.hu =3D-
-=3DTel/fax:+3612367353  &nbs= p;         |Mobil:+36209278894=           =3D-

---------------------------(end of broadcast)----------------------= -----
TIP 2: Don't 'kill -9' the postmaster
=



--
Robins ------=_Part_149290_20072295.1178905148825--