Thread: views with parameters

views with parameters

From
"Thomas Hermann(Software)"
Date:
Hi there,
i have read

http://archives.postgresql.org/pgsql-novice/2001-07/msg00098.php

which dates back to july 2001. Has pg developed any further -.. ?

i am trying to makes a project compatible for both ms sgl-server and
postgresql
by using stored procedures (ms) and views (pg) or functions.

Much to my dismay the documentation does not specify any
parameters/arguments which can be used with views.  But views do retun a
complete record or record sets as defined in pg.

One can send arguments with functions, but by whatever reason (and return
type), i have not been able to get anything back which compares to what
comes back from an ordinary select oder view. Best i got: a colon separated
string having all the data in one field (although a special type was
referenced etc.)


Is this the way it is .. or did i miss something. i thought i was looking
for something simple.

thanks in advance for every meaningful response, even if it breaks all my
dreams (but saves time).

Tom Hermann
hth (at) sprintsoft (dot) de




Re: views with parameters

From
Bruno Wolff III
Date:
On Sat, Nov 27, 2004 at 21:07:03 +0100,
  "Thomas Hermann(Software)" <hth@sprintsoft.de> wrote:
>
> Much to my dismay the documentation does not specify any
> parameters/arguments which can be used with views.  But views do retun a
> complete record or record sets as defined in pg.

That doesn't make much sense. Views look like tables. Tables don't
have arguments or parameters. If you want a subset of the records
defined by a view, you can use the where clause.

> One can send arguments with functions, but by whatever reason (and return
> type), i have not been able to get anything back which compares to what
> comes back from an ordinary select oder view. Best i got: a colon separated
> string having all the data in one field (although a special type was
> referenced etc.)

Recent versions from Postgres allow set returning functions. You can use these
in the from item list. I don't use these, but my memory is that there have
been some significant improvements for version 8.0, so you might want to
look at the 8.0 beta for your initial development. 8.0 will probably be
released in a month.

Re: views with parameters

From
Bruno Wolff III
Date:
On Sun, Nov 28, 2004 at 00:39:40 +0100,
  "Thomas Hermann(Software)" <hth@sprintsoft.de> wrote:

Usually you want to keep responses coppied to the list so that others
can comment on the thread.

> Thank you very much for your expedient response. I am currently running the
> 8 beta.
> 1) I want to modify the returned result set by sending different values used
> in the where clause, hence I get the desired information from the table.
> Assume I have the profile ID of a user an need to know the details of the
> profile, I can feed the profile to the view ID and get the proper details
> back. If the select statement within a view does not change, I can as well
> have select statement stored elsewhere.

You can supply the profile ID in a where clause when you select from the
view. Is there some reason you can't do that?

Re: views with parameters

From
"Thomas Hermann(Software)"
Date:
Bruno, thank you for your patience, but I still don't have a clou.
Let me illustrate this with a sample from MS SQL-Server:

CREATE PROCEDURE au_info_selpub
   @pubname varchar(40)
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
   WHERE  p.pub_name = @pubname

EXEC au_info_selpub 'Algodata Infosystems'

This sample refers to the "pubs" database used in several bokks on MS and
Sysbase.
It returns a recordset with data in 4 columns, including column names and
data types per column.
What would be the PostgreSQL equivalent? And would I get the same results?

Thank you again
Tom Hermann


-----Ursprungliche Nachricht-----
Von: Bruno Wolff III [mailto:bruno@wolff.to]
Gesendet: Montag, 29. November 2004 17:34
An: Thomas Hermann(Software)
Cc: pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] views with parameters


On Sun, Nov 28, 2004 at 00:39:40 +0100,
  "Thomas Hermann(Software)" <hth@sprintsoft.de> wrote:

Usually you want to keep responses coppied to the list so that others
can comment on the thread.

> Thank you very much for your expedient response. I am currently running
the
> 8 beta.
> 1) I want to modify the returned result set by sending different values
used
> in the where clause, hence I get the desired information from the table.
> Assume I have the profile ID of a user an need to know the details of the
> profile, I can feed the profile to the view ID and get the proper details
> back. If the select statement within a view does not change, I can as well
> have select statement stored elsewhere.

You can supply the profile ID in a where clause when you select from the
view. Is there some reason you can't do that?


Re: views with parameters

From
Sean Davis
Date:
On Nov 30, 2004, at 4:31 AM, Thomas Hermann(Software) wrote:

> Bruno, thank you for your patience, but I still don't have a clou.
> Let me illustrate this with a sample from MS SQL-Server:
>
> CREATE PROCEDURE au_info_selpub
>    @pubname varchar(40)
> AS
> SELECT au_lname, au_fname, title, pub_name
>    FROM authors a INNER JOIN titleauthor ta
>       ON a.au_id = ta.au_id INNER JOIN titles t
>       ON t.title_id = ta.title_id INNER JOIN publishers p
>       ON t.pub_id = p.pub_id
>    WHERE  p.pub_name = @pubname
>
> EXEC au_info_selpub 'Algodata Infosystems'
>

Why not:

create view au_info_selpub AS SELECT au_lname, au_fname,title,pub_name
      FROM authors a INNER JOIN titleauthor ta
       ON a.au_id = ta.au_id INNER JOIN titles t
       ON t.title_id = ta.title_id INNER JOIN publishers p
       ON t.pub_id = p.pub_id;

Then:

select * from au_info_selfpub where pub_name='Algodata Infosystems';

Sean



Re: views with parameters

From
Tom Lane
Date:
Sean Davis <sdavis2@mail.nih.gov> writes:
> On Nov 30, 2004, at 4:31 AM, Thomas Hermann(Software) wrote:
>> Bruno, thank you for your patience, but I still don't have a clou.
>> Let me illustrate this with a sample from MS SQL-Server:
>>
>> CREATE PROCEDURE au_info_selpub
>> @pubname varchar(40)
>> AS
>> SELECT au_lname, au_fname, title, pub_name
>> FROM authors a INNER JOIN titleauthor ta
>> ON a.au_id = ta.au_id INNER JOIN titles t
>> ON t.title_id = ta.title_id INNER JOIN publishers p
>> ON t.pub_id = p.pub_id
>> WHERE  p.pub_name = @pubname
>>
>> EXEC au_info_selpub 'Algodata Infosystems'

> Why not:

> create view au_info_selpub AS SELECT au_lname, au_fname,title,pub_name
>       FROM authors a INNER JOIN titleauthor ta
>        ON a.au_id = ta.au_id INNER JOIN titles t
>        ON t.title_id = ta.title_id INNER JOIN publishers p
>        ON t.pub_id = p.pub_id;

> Then:

> select * from au_info_selfpub where pub_name='Algodata Infosystems';

That would be my recommendation too -- a view is way more flexible than
a function (for instance, you could use other WHERE tests with it).

However, if you really really want a function, it would look something
like

create type au_info_selpub_type (au_lname varchar, au_fname varchar,
                                 title varchar, pub_name varchar);
-- I'm guessing about the column data types here, obviously

create function au_info_selpub(varchar) returns setof au_info_selpub_type
as 'SELECT au_lname, au_fname, title, pub_name
    FROM authors a INNER JOIN titleauthor ta
    ON a.au_id = ta.au_id INNER JOIN titles t
    ON t.title_id = ta.title_id INNER JOIN publishers p
    ON t.pub_id = p.pub_id
    WHERE  p.pub_name = $1'
language sql;

select * from au_info_selpub('Algodata Infosystems');

            regards, tom lane

Re: views with parameters

From
"Thomas Hermann(Software)"
Date:
Sean, I see the point.
Thank you
Tom Hermann

-----Ursprungliche Nachricht-----
Von: Sean Davis [mailto:sdavis2@mail.nih.gov]
Gesendet: Dienstag, 30. November 2004 16:50
An: Thomas Hermann(Software)
Cc: Bruno Wolff III; pgsql-novice@postgresql.org
Betreff: Re: [NOVICE] views with parameters



On Nov 30, 2004, at 4:31 AM, Thomas Hermann(Software) wrote:

> Bruno, thank you for your patience, but I still don't have a clou.
> Let me illustrate this with a sample from MS SQL-Server:
>
> CREATE PROCEDURE au_info_selpub
>    @pubname varchar(40)
> AS
> SELECT au_lname, au_fname, title, pub_name
>    FROM authors a INNER JOIN titleauthor ta
>       ON a.au_id = ta.au_id INNER JOIN titles t
>       ON t.title_id = ta.title_id INNER JOIN publishers p
>       ON t.pub_id = p.pub_id
>    WHERE  p.pub_name = @pubname
>
> EXEC au_info_selpub 'Algodata Infosystems'
>

Why not:

create view au_info_selpub AS SELECT au_lname, au_fname,title,pub_name
      FROM authors a INNER JOIN titleauthor ta
       ON a.au_id = ta.au_id INNER JOIN titles t
       ON t.title_id = ta.title_id INNER JOIN publishers p
       ON t.pub_id = p.pub_id;

Then:

select * from au_info_selfpub where pub_name='Algodata Infosystems';

Sean



Re: views with parameters

From
"Thomas Hermann(Software)"
Date:
Tom, this last message finally turned me into the right direction
thank you once again
Tom Hermann

-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Dienstag, 30. November 2004 19:05
An: Thomas Hermann(Software)
Betreff: Re: AW: [NOVICE] views with parameters


> My experience with the function although (in 8.0 beta) is,
> that it returns not the same data a the view would return.
> It returns a comma separated text string, although the requested data is
> presented.

There's a difference between "select foo()" and "select * from foo()".
The first returns a single composite column.

            regards, tom lane