Re: parameterized views? - Mailing list pgsql-general

After second thoughts a cursor is not what you are looking for i think

Sorry for the dead end

Darren

On Tue, 3 Sep 2002, Linn Kubler wrote:

> Hi Darran,
>
> Thanks for responding.  I'm unfamiliar with a REFCURSOR, can you give me a
> brief explanation?  Do you know where in the documetation I can find
> information about it?
>
> Thanks,
> Linn
>
> "Darren Ferguson" <darren@crystalballinc.com> wrote in message
> news:Pine.LNX.4.44.0209031311330.15154-100000@thread.crystalballinc.com...
> > 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@joeconway.com> wrote in message
> > > news:3D743B44.2080601@joeconway.com...
> > > > 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
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Darren Ferguson