SQL View with Parameter in PostgreSQL, best way to prevent SQL error on runtime?

Question

Is it possible to create view with parameter in PostgreSQL?

the problem was, there probably unverified SQL syntax in our backend services that can cause an internal server error, so probably I'll need to convert every queries to views?

Something like this:

query := ` --
   func Bla_ByType
SELECT id
  , data
    -
    >>
    'name'
FROM bla
WHERE data->>'type' = ` + escapeAndQuote(param1)

Syntax above not detected as error because it's just a string, the correct one would be:

query := ` -- func Bla_ByType
SELECT id
  , data->>'name'
FROM bla
WHERE data->>'type' = ` + escapeAndQuote(param1)

The example above just a simple query, the other's are longer, something like this:

WITH t AS (
   SELECT ...
   FROM 
   WHERE param1
) SELECT ... 
FROM ...
LEFT JOIN t 
WHERE param2`

Is there any alternative other than reading session:

CREATE OR REPLACE VIEW v_bla_bytype AS 
SELECT id
  , data->>'name'
FROM bla
WHERE data->>'type' = CAST(current_setting('bla_bytype._type') as TEXT)

SET bla_bytype._type = 'test';
SELECT * FROM v_bla_bytype;

the problem with session variable is still will got an error, something like: ERROR: unrecognized configuration parameter "bla_bytype._type" if the programmer forgot to set the session variable.

Or using stored procedure:

CREATE OR REPLACE FUNCTION p_bla_bytype(_type text)
RETURNS TABLE (id bigint, name text) AS $$
  SELECT id
  , data->>'name'
  FROM bla
  WHERE data->>'type' = $1 
$$ LANGUAGE sql;
-- i don't know hot to use "_type" by name not using number ($1)

SELECT * FROM p_bla_bytype('test');

The problem with stored procedure was the column names must be typed 2 times (one in the SELECT, one in the RETURNS), we also need to declare the data types.

Is there any alternative/shorter solution other than those two? something like this maybe:

CREATE OR REPLACE PARAMETERIZED VIEW pv_bla_bytype(_type TEXT) AS
  SELECT id
    , data->>'name' "name"
  FROM bla
  WHERE data->>'type' = $_type
;

SELECT * FROM pv_bla_bytype('test');

The solution should have:

good performance
sql syntax verified
not too much typing

Is there such solution in PostgreSQL?

You don't need to convert your queries to views to avoid syntax errors. You need to test them. — BillRobertson42, Jun 22 '16 at 03:13
Why not use a function? http://stackoverflow.com/q/11401749/330315 — , Jun 22 '16 at 05:26
As it is posted it looks like you created a problem out of nowhere. What is the real problem? — Clodoaldo Neto, Jun 22 '16 at 12:10
the real problem when copy pasting from tested SQL to the backend source code, it`s automatically formatted, and apparently became wrong if the backend codes missing 1 quote (it affect next SQL statement) — Kokizzu, Jun 22 '16 at 16:51

score 5 · Answer 1 · answered Jun 22 '16 at 07:40

try function?..

CREATE OR REPLACE function pv_bla_bytype(_type TEXT) returns table (id bigint, name text) AS 
$F$
declare
begin
return query 
  SELECT bla.id
    , data->>'name' "name"
  FROM bla
  WHERE data->>'type' = _type
;
end;
$F$
language plpgsql
;
SELECT * FROM pv_bla_bytype('test');

SQL View with Parameter in PostgreSQL, best way to prevent SQL error on runtime?

1 Answers1