Thread: views with parameters
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
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.
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?
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?
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
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
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
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