How to implement dynamic sql function in postgresql?

Question

I am using PostgreSQL 11 version.

I want to implement a function that takes the layer name (table), column name, and id as parameters.

create or replace function test(layer_name anyelement, field_name anyelement, object_id text)
   returns setof anyelement
   language plpgsql
as $function$
   begin 
       return query execute format('
           select 
            *
           from
            %s
           where
            %s = cast($1 as int4)'
    , pg_typeof(layer_name), pg_typeof(field_name))
    using object_id;
end;
$function$
;

This is the code I've implemented and when I call the function I get an error.

What am I doing wrong?

Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="

Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY

### The error occurred while setting parameters
### SQL: select * from test(?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="
  Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "="
  Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY}
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="
  Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY


### The error occurred while setting parameters
### SQL: select * from test(?, ?, ?)
### Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="
  Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "="
  Where: PL/pgSQL function test(anyelement,anyelement,text) line 3 at RETURN QUERY

What is layer and why you are using pg_typeof(layer_name)? — Akhilesh Mishra
– Akhilesh Mishra, Commented Sep 1, 2020 at 6:38
The code makes too little sense for me to understand. Can you explain what you are trying to do? — Laurenz Albe
– Laurenz Albe, Commented Sep 1, 2020 at 6:42
I want to make a function that takes the table name, the column name of the conditional clause, and the condition ID as parameters and returns the result. — Hans
– Hans, Commented Sep 1, 2020 at 7:02
@Hans You are very close. Just few things need to be changed. Added answer — Akhilesh Mishra
– Akhilesh Mishra, Commented Sep 1, 2020 at 7:50

Akhilesh Mishra · Accepted Answer · 2020-09-01 08:17:25Z

2

You have to change your function slightly

Instead of field_name anyelement use field_name text in parameter.
and in place of pg_typeof(field_name) use only field_name:

So your function definition will be:

create or replace function test(layer_name anyelement, field_name text, object_id text)
   returns setof anyelement
   language plpgsql
as $function$
   begin 
       return query execute format('
           select 
            *
           from
            %s
           where
            %s = cast($1 as int4)'
    , pg_typeof(layer_name), field_name)
    using object_id;
end;
$function$
;

Most important part is calling of the function:

select * from test(null::table_name,'field_name','2');

Please note that your field_name always should be integer type and object_id should be number only because you are casting it to integer.

DEMO

edited Sep 1, 2020 at 8:17

answered Sep 1, 2020 at 7:43

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

4 Comments

Hans Over a year ago

I don't know what null:: means but It worked very successfully. thank you

Andi Over a year ago

For the sake of completeness: ::<datatype> casts something to a specific datatype. Casting a null value to a datatype simply is the easiest way to get a value of a specific datatype. Casting for example an integer to other data types requires some kind of calculations to be done. And these calculations are different for each data type or impossible for e.g. a timestamp. Casting nulls to anything is easy.

Mohit Sharma Over a year ago

Can it able to handle conditions like - AND | OR , clauses like - ORDER BY | GROUP BY

Akhilesh Mishra Over a year ago

Never used but it should work

Collectives™ on Stack Overflow

How to implement dynamic sql function in postgresql?

Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: syntax error at or near "="

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related