Postgres Dynamic Query Function

Postgres Dynamic Query Function

You cannot use a variable in place of an identifier like that. You need to do it with dynamic queries. It will look something like this:

EXECUTE 'SELECT * FROM ' || quote_ident(tname) 
        || ' WHERE ' || quote_ident(cname) || ' NOT IN (''AK'',''CK'');'
INTO result_var;

If you are using PostgreSQL 9.1 or above, you can use the format() function which makes constructing this string much easier.

How to implement dynamic sql function in postgresql?

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

How to Run Multiple Dynamic Queries in a PostgreSQL Function

You can't simply concatenate strings to make a dynamic sql statement. Take a look at EXECUTE and EXECUTE IMMEDIATE.

In your case, you could use it like this:

CREATE OR REPLACE FUNCTION cnms_fy22q2.test_function(
    fyq text)
    RETURNS void
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
BEGIN
    -- logic
    EXECUTE 'TRUNCATE TABLE schema_' || fyq || '.my_table';
    EXECUTE 'DROP TABLE schema_' || fyq || '.my_table';
END
$BODY$; 

Postgresql execute dynamic query inside function return NULL

Your code is working on my database. Maybe you are executing different instance of this function - PostgreSQL allows function overloading, and some mysterious bugs are based on more functions with same name.

I am pretty dislike this kind of functions - you try to hide SQL - and the usual result if this technique is pretty slow applications, but it is your life :). With modern PostgreSQL you can write your function little bit more readable:

CREATE OR REPLACE FUNCTION foo(_field text, _table text, _id int,
                               resulttypedval anyelement, OUT _result anyelement)
AS $$
BEGIN
  EXECUTE format('SELECT %I FROM %I'
                      ' WHERE $1 IS NULL OR id = $1',
                  _field, _table)
     INTO _result
     USING _id;
  RETURN;
END;
$$ LANGUAGE plpgsql;  

Print prepared dynamic query with result set in Postgresql function using DBeaver

You can use RAISE NOTICE for messages.

CREATE OR REPLACE FUNCTION fn_testing
                           (_tbl_name name)
                           RETURNS TABLE
                                   (col1 integer,
                                    col2 text)
AS
$$
DECLARE
  _query text;
BEGIN
  _query := format('SELECT col1, col2 FROM %I;', _tbl_name);
  RAISE NOTICE '%', _query;
  RETURN QUERY EXECUTE _query;
END;
$$
LANGUAGE plpgsql;

Note: There's a special type, name, for identifiers. And to prevent SQL injection or errors you should make sure the dynamic identifiers are properly quoted. You can use format() with %I for that.

dynamic sql query in postgres

EXECUTE ... USING only works in PL/PgSQL - ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.

Compare:

• PL/PgSQL's EXECUTE ... USING; to
• SQL's EXECUTE

See the 2nd last par in my prior answer.

---

In addition to not running except in PL/PgSQL your SQL statement is wrong, it won't do what you expect. If (select id from ids where condition = some_condition) returns say 42, the statement would fail if id is an integer. If it's cast to text you'd get:

EXECUTE format('SELECT * from result_%s_table', quote_ident('42'));
EXECUTE format('SELECT * from result_%s_table', '"42"');
EXECUTE 'SELECT * from result_"42"_table';

That's invalid. You actually want result_42_table or "result_42_table". You'd have to write something more like:

EXECUTE format('SELECT * from %s', quote_ident('result_'||(select id from ids where condition = some_condition)||'_table'))

... if you must use quote_ident.

Trying to create dynamic query strings with PL/PgSQL to make DRY functions in PostgreSQL 9.6

The column names and tables names should not be used as query parameters passed by USING clause.

Probably lines:

RETURN QUERY 
    EXECUTE format('SELECT $1 FROM %I', tbl)
    USING query_string;

should be:

RETURN QUERY 
    EXECUTE format('SELECT %s FROM %I', query_string, tbl);

This case is example why too DRY principle is sometimes problematic. If you write it directly, then your code will be simpler, cleaner and probably shorter.

Dynamic SQL is one from last solution - not first. Use dynamic SQL only when your code will be significantly shorter with dynamic sql than without dynamic SQL.

How to use execute dynamic query into int array in Postgresql

You need to aggregate the values into an array in order to store them in an array variable.

Additionally: you shouldn't pass parameters as strings, pass them with the USING clause:

EXECUTE format(
'
   SELECT array_agg(DISTINCT m.id)
   FROM Part p
   JOIN %1$s A ON A.part_id = p.id
   JOIN Model m ON m.%1$s_id = A.id
   WHERE p.id = $1
  ',
   trim(NEW.part_type)
)
INTO model_ids    
USING NEW.id;

---

Related Topics