Thread: parameterized views?
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. Thanks in advance, Linn
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
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
Linn Kubler wrote:
> Thanks for responding Joe but, not exactly. I'm looking for something
> 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;
I believe the optimizer will see this as exactly the same query as
create view myview as
select f1, f2, f3 from mytable;
select * from myview where f3 = 15;
so I don't think you'd get any different execution time.
Are you looking to avoid parsing overhead, similar to a prepared
statement (also new in 7.3)? In any case what you're looking for does
not exist currently, and I don't know of anyone working on it.
> Something like that. Returning sets from a function looks promising as
> you described below but, 7.3 seems like it's a long way off if it's just
> going to beta now.
It depends how you define "a long way off". I'd *guess* 7.3 will be
released within about 2 months of starting beta -- but no promises of
course.
Joe
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
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
Joe Conway <mail@joeconway.com> writes: > Linn Kubler wrote: > > Thanks for responding Joe but, not exactly. I'm looking for something > > 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; > > I believe the optimizer will see this as exactly the same query as > create view myview as > select f1, f2, f3 from mytable; > select * from myview where f3 = 15; > so I don't think you'd get any different execution time. What you're describing is something I've often wished existed but I've nearly always realized I didn't really need. I suspect it runs somewhat contrary to the design of SQL. To find the way around the problem as Joe Conway demonstrated usually requires wrapping your head around the idea of having your view represent results for all possible values of your parameter and then putting a where clause on the select from the view. You should be able to count on a good database optimizer to push the where clause into the view and not do more work than necessary. This keeps the concept of a view as just an imaginary table with consistent contents regardless of who looks at it. It also ends up being more flexible in the end than parameters like you describe. -- greg
"Joe Conway" <mail@joeconway.com> wrote in message news:3D74E5E5.9070309@joeconway.com... > Linn Kubler wrote: > > Thanks for responding Joe but, not exactly. I'm looking for something > > 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; > > I believe the optimizer will see this as exactly the same query as > create view myview as > select f1, f2, f3 from mytable; > select * from myview where f3 = 15; > so I don't think you'd get any different execution time. > > Are you looking to avoid parsing overhead, similar to a prepared > statement (also new in 7.3)? In any case what you're looking for does > not exist currently, and I don't know of anyone working on it. > > > Something like that. Returning sets from a function looks promising as > > you described below but, 7.3 seems like it's a long way off if it's just > > going to beta now. > > It depends how you define "a long way off". I'd *guess* 7.3 will be > released within about 2 months of starting beta -- but no promises of > course. > > Joe It's not execution time that I'm trying to save here, that isn't an issue for my database. I'm looking to have a view defined where I can get a subset of the records returned based on a parameter. Sure would be a handy feature for me. The other option I suppose is to have multiple views defiened for each senario and then have the front end pick the appropriate view. That just seems like a lot of work and won't be as flexible. Thanks again, Linn
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
Linn Kubler sez: } } "Joe Conway" <mail@joeconway.com> wrote in message } news:3D74E5E5.9070309@joeconway.com... } > Linn Kubler wrote: } > > Thanks for responding Joe but, not exactly. I'm looking for something } > > more like this: } > > } > > create view myview as } > > select f1, f2, f3 from mytable where f3 = $1; [...] } It's not execution time that I'm trying to save here, that isn't an issue } for my database. I'm looking to have a view defined where I can get a } subset of the records returned based on a parameter. Sure would be a handy } feature for me. The other option I suppose is to have multiple views } defiened for each senario and then have the front end pick the appropriate } view. That just seems like a lot of work and won't be as flexible. It's not clear to me what you are expecting to gain from a parameterized view. I'll grant you that it seems like a nice idea (though the line between a parameterized view and a function that can return sets of rows is pretty fuzzy), but I think you can get much the same effect without such hoop-jumping. If you want to simplify the SELECT or FROM clause, you can use a view. If you want to simplify the WHERE clause you can use a function. If it's both, use both. For example, suppose you want the effect of your view above. Try the following (assuming that f3 is a text type): CREATE VIEW myview AS ( SELECT f1, f2, f3 FROM mytable ); CREATE FUNCTION myfilter(text, text) RETURNS boolean AS ' select $1 = $2' LANGUAGE SQL; To use it, you would write: SELECT * FROM myview WHERE myfilter(f3, 'paramvalue'); The view and the function can be arbitrarily complex, and all you have to do is pass the right parameters into the function. Does this solve your problem? Note that I don't think the optimizer is clever enough to delve into the function's plan and use an index rather than a table scan. } Thanks again, } Linn --Greg
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
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