Skip site navigation (1) Skip section navigation (2)

Re: views with parameters

From: "Thomas Hermann(Software)" <hth(at)sprintsoft(dot)de>
To: "Bruno Wolff III" <bruno(at)wolff(dot)to>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: views with parameters
Date: 2004-11-30 09:31:00
Message-ID: BLEJJCIDDLINMPGGCKFCOEHKCDAA.hth@sprintsoft.de (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)wolff(dot)to]
Gesendet: Montag, 29. November 2004 17:34
An: Thomas Hermann(Software)
Cc: pgsql-novice(at)postgresql(dot)org
Betreff: Re: [NOVICE] views with parameters


On Sun, Nov 28, 2004 at 00:39:40 +0100,
  "Thomas Hermann(Software)" <hth(at)sprintsoft(dot)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?


In response to

Responses

pgsql-novice by date

Next:From: Kaloyan Iliev IlievDate: 2004-11-30 10:34:20
Subject: Re: automatically lower string and remove unnecessary whitespace
Previous:From: Marcin GilDate: 2004-11-30 08:48:07
Subject: Re: problematic upgrade

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group