Re: parameterized views? - Mailing list pgsql-general

YES! That's it!  I just realized that I made a small error in my example.
What I actually coded was:

create view myview as select f1, f2 from mytable;

Then I put together a select like this:

select * from myview where f3 = 15;

No wonder it wasn't working, there was no f3 column to apply the 'where'
too.  When I changed my view to include f3 everything started working.
That's much better.

My confusion stems from my Visual FoxPro experience.  They have a
parameterized view, goes like this:

create view myview as select f1, f2 from mytable where f3 = ?myparam
(where f3 is a column in mytable)

Then I use it like this:
myparam = 15
use myview

And it returns a view as I was expecting.  The I can see one advantage in
the VFP style and that is that it returns only the columns I'm interested,
in this example f1 and f2.  In Postgres I have to return the f3 column as
well but that's not a big deal, it's not a lot of data and I can ignor it.

I like the idea of using a function too, I'll be looking into that when I
upgrade to 7.3.

Thanks to all who responded,
Linn

"Jeff Davis" <list-pgsql-general@empires.org> wrote in message
news:200209031417.32294.list-pgsql-general@empires.org...
> If that's all you need to do will just defining a view on the first part
work?
> i.e.:
> create view myview as select f1, f2, f3 from mytable;
> then you can do:
> select * from myview where f3 = 15;
>
> Regards,
> Jeff Davis
>
> On Tuesday 03 September 2002 09:04 am, 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org