Обсуждение: 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)