pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/PGSQL Record type question
7+ messages / 4 participants
[nested] [flat]
* PL/PGSQL Record type question
@ 2007-05-11 14:00 Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 14:09 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
2007-05-11 17:39 ` Re: PL/PGSQL Record type question Robins <tharakan@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Gábriel Ákos @ 2007-05-11 14:00 UTC (permalink / raw)
To: pgsql-sql
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 =-
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2007-05-11 14:09 ` imad <immaad@gmail.com>
2007-05-11 14:42 ` Re: PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
1 sibling, 1 reply; 7+ messages in thread
From: imad @ 2007-05-11 14:09 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-sql
You might be looking for PostgreSQL RECORD data type.
--Imad
www.EnterpriseDB.com
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 14:09 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
@ 2007-05-11 14:42 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 15:12 ` Re: PL/PGSQL Record type question John DeSoi <desoi@pgedit.com>
2007-05-11 16:40 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Gábriel Ákos @ 2007-05-11 14:42 UTC (permalink / raw)
To: pgsql-sql
On Fri, 11 May 2007 19:09:07 +0500
imad <immaad@gmail.com> wrote:
> You might be looking for PostgreSQL RECORD data type.
Thanks. Give me an example please. I saw the documentation already.
>
> --Imad
> www.EnterpriseDB.com
>
> 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.
--
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabriel@i-logic.hu|Web: http://www.i-logic.hu =-
-=Tel/fax:+3612367353 |Mobil:+36209278894 =-
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 14:09 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
2007-05-11 14:42 ` Re: PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2007-05-11 15:12 ` John DeSoi <desoi@pgedit.com>
1 sibling, 0 replies; 7+ messages in thread
From: John DeSoi @ 2007-05-11 15:12 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-sql
You can use CREATE TYPE:
http://www.postgresql.org/docs/8.2/interactive/sql-createtype.html
Example from the documentation:
CREATE TYPE compfoo AS (f1 int, f2 text);
Then make your function return compfoo (or setof compfoo).
Alternately, you can define your function with out or in/out
parameters so you don't need the CREATE TYPE statement.
On May 11, 2007, at 10:42 AM, Gábriel Ákos wrote:
>> You might be looking for PostgreSQL RECORD data type.
>
> Thanks. Give me an example please. I saw the documentation already.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 14:09 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
2007-05-11 14:42 ` Re: PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2007-05-11 16:40 ` imad <immaad@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: imad @ 2007-05-11 16:40 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-sql
create a function with return type as a RECORD.
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);
Do you like that ... :-)
--Imad
www.EnterpriseDB.com
On 5/11/07, Gábriel Ákos <akos.gabriel@i-logic.hu> wrote:
> On Fri, 11 May 2007 19:09:07 +0500
> imad <immaad@gmail.com> wrote:
>
> > You might be looking for PostgreSQL RECORD data type.
>
> Thanks. Give me an example please. I saw the documentation already.
>
> >
> > --Imad
> > www.EnterpriseDB.com
> >
> > 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.
>
>
>
> --
> Ü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 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2007-05-11 17:39 ` Robins <tharakan@gmail.com>
2007-05-11 18:19 ` Re: PL/PGSQL Record type question imad <immaad@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Robins @ 2007-05-11 17:39 UTC (permalink / raw)
To: Gábriel Ákos <akos.gabriel@i-logic.hu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: PL/PGSQL Record type question
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 17:39 ` Re: PL/PGSQL Record type question Robins <tharakan@gmail.com>
@ 2007-05-11 18:19 ` imad <immaad@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: imad @ 2007-05-11 18:19 UTC (permalink / raw)
To: robins@pobox.com; +Cc: Gábriel Ákos <akos.gabriel@i-logic.hu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-05-11 18:19 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-11 14:00 PL/PGSQL Record type question Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 14:09 ` imad <immaad@gmail.com>
2007-05-11 14:42 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2007-05-11 15:12 ` John DeSoi <desoi@pgedit.com>
2007-05-11 16:40 ` imad <immaad@gmail.com>
2007-05-11 17:39 ` Robins <tharakan@gmail.com>
2007-05-11 18:19 ` imad <immaad@gmail.com>
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