From: Robins <tharakan@gmail.com>
To: Gábriel Ákos <akos.gabriel@i-logic.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: PL/PGSQL Record type question
Date: Fri, 11 May 2007 23:09:08 +0530
Message-ID: <36af4bed0705111039r561f1c3bod9183535ed86763@mail.gmail.com> (raw)
In-Reply-To: <20070511160037.1c09160e@gabriel-laptop>
References: <20070511160037.1c09160e@gabriel-laptop>
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.
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().
Personally, I have tried both and believe the second way (TYPE) is quite
convenient for me.
Regards,
Robins Tharakan
On 5/11/07, Gábriel Ákos <akos.gabriel@i-logic.hu> 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
>
>
>
> --
> Üdvözlettel,
> Gábriel Ákos
> -=E-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu =-
> -=Tel/fax:+3612367353 |Mobil:+36209278894 =-
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Robins
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tharakan@gmail.com, akos.gabriel@i-logic.hu
Subject: Re: PL/PGSQL Record type question
In-Reply-To: <36af4bed0705111039r561f1c3bod9183535ed86763@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox