Обсуждение: Function, that returns set of 2 tables columns
Hi * I need to write a function, that returns a set of all columns from 2 tables. e.g. I create such a function: CREATE OR REPLACE FUNCTION func(val_ int8) RETURNS SETOF record AS $BODY$ DECLARE i record; BEGIN for i in select * from "Table1", "Table2" loop return next i; end loop; return; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION func(val_ int8) OWNER TO postgres; But when I try to call it like this: SELECT * FROM func1(10); I have an error: ERROR: a column definition list is required for functions returning "record" This two tables have about 20 columns together, so I don't want list them each I call this function. Can I achieve this in some other (right) way? P.S. I don't want to create a specific type for this purpose, but if there is no other way, I should. Thanks in advance. -- Best regards, Dmitriy Chumack mailto:saint.d.a@gmail.com
am Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes: > Hi * > > I need to write a function, that returns a set of all columns from 2 > tables. > > e.g. I create such a function: > > CREATE OR REPLACE FUNCTION func(val_ int8) > RETURNS SETOF record AS > $BODY$ > DECLARE > i record; > BEGIN > > for i in select * from "Table1", "Table2" Use something like: select * from table1 union all select * from table2 Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
You need to specify and cast explicitly from your calling statement: SELECT * FROM func1(10) AS (col1 smallint, col2 bigint, col3 date);
On 2/13/07, Dmitriy Chumack <saint.d.a@gmail.com> wrote:
Hi *
I need to write a function, that returns a set of all columns from 2
tables.
e.g. I create such a function:
CREATE OR REPLACE FUNCTION func(val_ int8)
RETURNS SETOF record AS
$BODY$
DECLARE
i record;
BEGIN
for i in select * from "Table1", "Table2"
loop
return next i;
end loop;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION func(val_ int8) OWNER TO postgres;
But when I try to call it like this:
SELECT * FROM func1(10);
I have an error:
ERROR: a column definition list is required for functions returning
"record"
This two tables have about 20 columns together, so I don't want list
them each I call this function. Can I achieve this in some other
(right) way?
P.S. I don't want to create a specific type for this purpose, but
if there is no other way, I should.
Thanks in advance.
--
Best regards,
Dmitriy Chumack mailto: saint.d.a@gmail.com
---------------------------(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
Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer: > am Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes: >> Hi * >> >> I need to write a function, that returns a set of all columns from 2 >> tables. >> >> e.g. I create such a function: >> >> CREATE OR REPLACE FUNCTION func(val_ int8) >> RETURNS SETOF record AS >> $BODY$ >> DECLARE >> i record; >> BEGIN >> >> for i in select * from "Table1", "Table2" > Use something like: > select * from table1 union all select * from table2 > Andreas But how this solve my problem? I don't need standalone select, I need this select to be wrapped in function, and function has to return some type. -- Best regards, Dmitriy mailto:saint.d.a@gmail.com
am Tue, dem 13.02.2007, um 16:09:16 +0200 mailte Dmitriy Chumack folgendes: > Tuesday, February 13, 2007, 3:55:36 PM, Andreas Kretschmer: > > > am Tue, dem 13.02.2007, um 15:46:19 +0200 mailte Dmitriy Chumack folgendes: > >> Hi * > >> > >> I need to write a function, that returns a set of all columns from 2 > >> tables. > >> > >> e.g. I create such a function: > >> > >> CREATE OR REPLACE FUNCTION func(val_ int8) > >> RETURNS SETOF record AS > >> $BODY$ > >> DECLARE > >> i record; > >> BEGIN > >> > >> for i in select * from "Table1", "Table2" > > > Use something like: > > > select * from table1 union all select * from table2 > > > > Andreas > > > But how this solve my problem? I don't need standalone select, I need > this select to be wrapped in function, and function has to return some > type. Do you need the columns from both tables in one single column? Then you have to join this tables, i don't know how you want to join this tables. What i meant was: you can use this: for i in select * from "Table1" union all select * from "Table2" loop -- do something end loop; within a pl/pgsql-function. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 02/13/07 07:46, Dmitriy Chumack wrote: > Hi * > > I need to write a function, that returns a set of all columns from 2 > tables. [snip] > for i in select * from "Table1", "Table2" > loop > return next i; > end loop; [snip] > > This two tables have about 20 columns together, so I don't want list > them each I call this function. Can I achieve this in some other > (right) way? > > P.S. I don't want to create a specific type for this purpose, but > if there is no other way, I should. What do these tables join on? Creating a view might work. CREATE VIEW V_SIDE_BY_SIDE AS SELECT T1.*, T2.* FROM TABLE1 T1, TABLE2 T2 WHERE T1.PK = T2.PK; -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0ck4S9HxQb37XmcRAjPEAJ9qaHwa5mbKiFVqcfEZbPXIX7GmIwCgzvvs zCPM45xZ590kv2xXnIoSsqM= =NkbJ -----END PGP SIGNATURE-----
>Creating a view might work.
Yeah, if all you use is SELECT, you should probably use a view, then you don't need to specify the output columns in the calling statement, only a WHERE clause.
Othrewise, it's the same thing:
SELECT * FROM myview WHERE field1=10;
Views are transparant in postgresql, so you need not worry that at first your backend will load the whole view and then filter your results (performance-wise).
Yeah, if all you use is SELECT, you should probably use a view, then you don't need to specify the output columns in the calling statement, only a WHERE clause.
Othrewise, it's the same thing:
SELECT * FROM myview WHERE field1=10;
Views are transparant in postgresql, so you need not worry that at first your backend will load the whole view and then filter your results (performance-wise).
On 2/13/07, Ron Johnson <ron.l.johnson@cox.net> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 02/13/07 07:46, Dmitriy Chumack wrote:
> Hi *
>
> I need to write a function, that returns a set of all columns from 2
> tables.
[snip]
> for i in select * from "Table1", "Table2"
> loop
> return next i;
> end loop;
[snip]
>
> This two tables have about 20 columns together, so I don't want list
> them each I call this function. Can I achieve this in some other
> (right) way?
>
> P.S. I don't want to create a specific type for this purpose, but
> if there is no other way, I should.
What do these tables join on?
Creating a view might work.
CREATE VIEW V_SIDE_BY_SIDE AS
SELECT T1.*, T2.*
FROM TABLE1 T1,
TABLE2 T2
WHERE T1.PK = T2.PK;
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF0ck4S9HxQb37XmcRAjPEAJ9qaHwa5mbKiFVqcfEZbPXIX7GmIwCgzvvs
zCPM45xZ590kv2xXnIoSsqM=
=NkbJ
-----END PGP SIGNATURE-----
---------------------------(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