parameterized views?

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...

Show quoted text

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

"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)---------------------------

Show quoted text

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

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

Show quoted text

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

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)---------------------------

Show quoted text

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