CODEWITHSUNDEEP
postgresql
Hans
Hans

Reputation: 79

How to implement dynamic sql function in postgresql?

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

Upvotes: 2

Views: 1606

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

You have to change your function slightly

  1. Instead of field_name anyelement use field_name text in parameter.
  2. 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

Upvotes: 2

Related Questions