Hi sylvain,
i think what you need is a so-called "set-returning-function":
if you just need sql (example with a table called user_data):
create or replace function user_info(integer)
returns setof user_data as '
select * from user_data where user_id = $1;
' language 'sql';
if you need plpgsql:
create or replace function user_info(integer)
returns setof user_data as '
declare
p_user_id alias for $1;
v_row record;
begin
for v_row in select * from user_data where user_id = p_user_id
loop
-- business logic here, eg. Some if-statements or
sub-queries
-- write a row to the result set
return next v_row;
end loop;
return;
' language 'plpgsql';
if you want to return rows that do not come from one single table you
will probably need to create a type:
create type user_data as (
user_id integer,
username varchar
);
you can then use that type in the "returns setof TYPE" clause of the
function.
Hope that helps. You should search for info about set-returning
functions for more details.
[color=blue]
>-----Ursprüngliche Nachricht-----
>Von:
pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>owner@postgresql.org] Im Auftrag von sferriol
>Gesendet: Dienstag, 24. Februar 2004 17:30
>An:
pgsql-general@postgresql.org
>Betreff: [GENERAL] dynamic views
>
>hello
>is it possible with postgres 7.2 or more, to define a dynamic view.
>For example, i have a table with a column 'user'
>and i want to define a view which gives infomrations from different
>tables but the user has to specifie the 'user' parameter when using a
>select to the view
>
>sylvain
>
>
>---------------------------(end of[/color]
broadcast)---------------------------[color=blue]
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to[/color]
majordomo@postgresql.org)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
