Thread: SELECT with Function

Hello,

This is my first message to pgsql-novice.

I create plpgsql function "myfunc(integer)" thats returns of type
record with values from table X (the function decides what record must
be select). The parameter is an keyprod existing in table X and in
table A.

It run well sending in psql:
SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric);

The problem is:
I need return data from table A (this have column 'keyprod'). This
value (keyprod) must be passed as parameter to myfunc(). The result
must be a union of columns selected from table A and result of
myfunc().

How to create this query? It's possible?

Thanks

Paulo Nievierowski

PS: Excuses my poor english.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Paulo Nievierowski wrote:
| PS: Excuses my poor english.
Your english is excellent. MY is poor!!

| I create plpgsql function "myfunc(integer)" thats returns of type
| record with values from table X (the function decides what record must
| be select). The parameter is an keyprod existing in table X and in
| table A.
|
| It run well sending in psql:
| SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2 numeric);
|
| The problem is:
| I need return data from table A (this have column 'keyprod'). This
| value (keyprod) must be passed as parameter to myfunc(). The result
| must be a union of columns selected from table A and result of
| myfunc().
|
| How to create this query? It's possible?
Yep.
Look at this:

drop table dupa cascade;
create table dupa (a int, b int, c int);
insert into dupa (a, b, c) values (1, 2, 3);
insert into dupa (a, b, c) values (2, 3, 4);

create or replace function ttt(int) returns record  as '
declare
~        r record;
begin
~        select * into r from dupa where ($1=a);
~        return r;
end;
' language 'plpgsql';

And then:

ojciec=# select * from ttt(1) as foo(aa int, bb int, cc int);
~ aa | bb | cc
- ----+----+----
~  1 |  2 |  3


Is this what you need?
- --
ojciec
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBXYnyCAdl/NTXOsERAs/EAKCUq26LmG9N36vW/WXGC4i92Ci4VwCdF+eS
fiiHtfCVDONxxldr4SC17TI=
=tahv
-----END PGP SIGNATURE-----

Dear Marcin

Thanks for your reply.
My created function running well as exposed by you in ttt() function
and this is no problem.
I need extend this query.

See:
TableA
prod_id        numeric(10)
prod_name   varchar(40)
...

My function, as your ttt(), return 3 values and value prod_id from
TableA must be parameter to function.
I like obtain as result:
prod_id, prod_name, v1, v2, v3
The first 2 columns is from TableA and v1,v2,v3 is from function.

How create a query that return this row? Or it is impossible?
I have tried with UNION and JOIN without success :-(

Excuse me: you is polish? From Poland?
I brazilian descendant polish.

Thanks

Paulo Nievierowski






On Fri, 01 Oct 2004 18:46:45 +0200, Marcin Piotr Grondecki wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Paulo Nievierowski wrote:
> | PS: Excuses my poor english.
> Your english is excellent. MY is poor!!
>
> | I create plpgsql function "myfunc(integer)" thats returns of type
> | record with values from table X (the function decides what record
> must
> | be select). The parameter is an keyprod existing in table X and in
> | table A.
> |
> | It run well sending in psql:
> | SELECT * FROM myfunc( 10006530 ) as (vret1 numeric, vret2
> numeric);
> |
> | The problem is:
> | I need return data from table A (this have column 'keyprod'). This
> | value (keyprod) must be passed as parameter to myfunc(). The
> result
> | must be a union of columns selected from table A and result of
> | myfunc().
> |
> | How to create this query? It's possible?
> Yep.
> Look at this:
>
> drop table dupa cascade;
> create table dupa (a int, b int, c int);
> insert into dupa (a, b, c) values (1, 2, 3);
> insert into dupa (a, b, c) values (2, 3, 4);
>
> create or replace function ttt(int) returns record  as '
> declare
> ~        r record;
> begin
> ~        select * into r from dupa where ($1=a);
> ~        return r;
> end;
> ' language 'plpgsql';
>
> And then:
>
> ojciec=# select * from ttt(1) as foo(aa int, bb int, cc int);
> ~ aa | bb | cc
> - ----+----+----
> ~  1 |  2 |  3