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
– BillRobertson42, Commented Jun 22, 2016 at 3:13
As it is posted it looks like you created a problem out of nowhere. What is the real problem? — Clodoaldo Neto
– Clodoaldo Neto, Commented Jun 22, 2016 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
– Kokizzu, Commented Jun 22, 2016 at 16:51

Vao Tsun · Accepted Answer · 2016-06-22 07:40:31Z

5

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

answered Jun 22, 2016 at 7:40

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related