Question 89257 in PostgreSQL Question posted in
The official documentation can be found here.

Creating a SQL "parameterized" query or view in Postgresql?

Here’s a view with a hard-coded equality:

create or replace view v1 as select * from mytable where x=3;

Is it possible create a "parameterized" equivalent of this in Postgresql?

create or replace SOMETHING v2(value) as
  select * from mytable where x=value;

(Update: We have good answers for Postgresql here, and none for the other databases, so I’m amending the question to be Posgtresql-specific.)

Tags:

3

Answers


  1. 0

    Postgresql version

    create or replace function v2(val int)
returns setof mytable language sql as $$
    select *
    from mytable
    where x = val;
$$;

select * from v2(3);
    Login or Signup to reply.
  2. 0

    That is very product dependent, but for postgres you can use functions, but views work the same way, so there are not really any need to do it

    and you can use materialized views as well https://www.postgresql.org/docs/current/sql-creatematerializedview.html

    CREATE tABLe mytable ( f1 int, f2 int, x int)

    CREATE TABLE

    CREATE FUNCTION v2(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ select f1,f2 from mytable where x=$1; $$
    LANGUAGE SQL;

SELECT * FROM v2(42);
  

    CREATE FUNCTION
    f1 f2 
    SELECT 0

    CREATE VIEW myview As select f1,f2,x from mytable

    CREATE VIEW

    SELECT f1,f2 FROM myview WHERE x = 42
    f1 f2 
    SELECT 0

    fiddle

    Login or Signup to reply.
  3. 0

    I’ll respond to the subject line of the question and suggest that you’ve answered your own question. A view is just a query, so you "parameterize" it the same way you would any other query–with a where statement.

    In the same way you would write:

    select a, b, c
from table1
where a=5;

    you can write:

    select a, b, c
from view1
where a=5;

    Suppose view one is defined as:

    create view1 as
   select *
   from table1;

    then the second query above is as if you wrote:

    select a, b, c
from (
    select *
    from table1
) as s  -- s for subquery, since postgres requires one to name this
where a=5;

    As you can see, these are all functionally equivalent.

    I hope this helps. If, on the other hand, you really are looking for a mechanism to create views, then as the other answers suggest, you’ll want to use a procedural language.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search