Re: parameterized views?

Not sure if this is any help but you could try returning a REFCURSOR

Again not sure but it may work

On Tue, 3 Sep 2002, Linn Kubler wrote:

> Thanks for responding Joe but, not exactly. I'm looking for something a
> little simpler, more like this:
>
> create view myview as
> select f1, f2, f3 from mytable where f3 = $1;
>
> And then be able to call the view passing it a parameter somehow. Possibly
> like:
>
> select * from myview where f3 = 15; (where 15 would replace $1)
>
> Something like that. Returning sets from a function looks promising but,
> 7.3 seems like it's a long way off if it's just going to beta now.
>
> Thanks again,
> Linn
>
> "Joe Conway" <mail(at)joeconway(dot)com> wrote in message
> news:3D743B44(dot)2080601(at)joeconway(dot)com(dot)(dot)(dot)
> > Linn Kubler wrote:
> > > Hi,
> > >
> > > Is it possible to have parameterized views? Guess I'm thinking of
> > > something like a posiitonal parameter in a view. If it is possible I'd
> > > sure appreciate an example.
> > >
> >
> > In 7.3 (starting beta this week) you can return sets (rows and columns)
> > from table functions. For example:
> >
> > test=# create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> > 'foo_pkey' for table 'foo'
> > CREATE TABLE
> > test=# insert into foo values(0,'a','{"a0","b0","c0"}');
> > INSERT 664851 1
> > test=# insert into foo values(1,'b','{"a1","b1","c1"}');
> > INSERT 664852 1
> > test=# insert into foo values(2,'c','{"a2","b2","c2"}');
> > INSERT 664853 1
> > test=# create or replace function get_foo(int) returns setof foo as
> > 'select * from foo where f1 > $1' language sql;
> > CREATE FUNCTION
> > test=# select * from get_foo(0);
> > f1 | f2 | f3
> > ----+----+------------
> > 1 | b | {a1,b1,c1}
> > 2 | c | {a2,b2,c2}
> > (2 rows)
> >
> > Is this what you're looking for?
> >
> > HTH,
> >
> > Joe
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Darren Ferguson