Definition of return types for own functions?
Hello all!
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?
For example: if i want a function that returns a date and an integer, i
create the function:
CREATE FUNCTION bla(text) RETURNS SETOF table_name AS '...
And i need the table table_name with the structure:
CREATE TABLE table_name( datum DATE, zahl INTEGER);
Can i somehow define this structe inside the function declaration without
having some empty tables or views around?
Thank you all!
Best regards,
Matthias
am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte Matthias.Pitzl@izb.de folgendes:
Hello all!
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?
Yes, you can define a new type:
CREATE TYPE name AS
( attribute_name data_type [, ... ] )
HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, Sep 28, 2006 at 10:15:19AM +0200, A. Kretschmer wrote:
am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte Matthias.Pitzl@izb.de folgendes:
Hello all!
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?Yes, you can define a new type:
CREATE TYPE name AS
( attribute_name data_type [, ... ] )
Also, in more recent versions (8.1 I think) you can use OUT parameters
to create anonymous types.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
am Thu, dem 28.09.2006, um 12:31:37 +0200 mailte Martijn van Oosterhout folgendes:
On Thu, Sep 28, 2006 at 10:15:19AM +0200, A. Kretschmer wrote:
am Thu, dem 28.09.2006, um 9:46:29 +0200 mailte Matthias.Pitzl@izb.de folgendes:
Hello all!
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?Yes, you can define a new type:
CREATE TYPE name AS
( attribute_name data_type [, ... ] )Also, in more recent versions (8.1 I think) you can use OUT parameters
to create anonymous types.
Oh yes, thanks. A little example:
test=# create function foo(OUT b text, OUT i int) as $$begin b := 'foo'; i:=1; end; $$ language plpgsql;
CREATE FUNCTION
test=*# select * from foo();
b | i
-----+---
foo | 1
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
eMail schreiben kann jeder -- lernen: http://webserv/email/email.html
Matthias.Pitzl@izb.de wrote:
Is it possible to define a complex return type like a record in a function
without having some table around with the needed structure of the return
values?
Sure, you just have to specify the columns at select time instead (and
this is easy enough to wrap inside a view).
CREATE FUNCTION foo(TEXT) RETURNS SETOF RECORD AS '...' LANGUAGE 'plpgsql' ;
SELECT * FROM foo('bar') AS x(a,b,c,d) ;
Regards,
LL