Thread: 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
On Tuesday 24 February 2004 16:29, sferriol wrote: > 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 You can have a view something like: SELECT * FROM personal_info WHERE owner = CURRENT_USER; There are some other vars/functions too SESSION_USER and current_database() etc. -- Richard Huxton Archonet Ltd
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. >-----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 broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Sorry i forgot to mention: -that you need pg 7.3 for the set-returning function feature. -how to select from the function: select * from _YOUR_FUNCTION(PARAMETER); and NOT: select _YOUR_FUNCTION(PARAMETER); >-----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 broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)