PostgreSQL 9.3: Pass values to only selected parameter in function

Question

I have to table with four columns as shown below:

Table:

create table fortesting
(
    cola int not null,
    colb timestamp null,
    colc text null,
    cold int null
)

Now I want to create a function to insert the records into the table with the dynamic sql so I used language plpgsql function as shown below.

Function:

create or replace function functionTest
(
    p_cola int,
    p_colb timestamp = null,
    p_colc text = '',
    p_cold int = null
)
returns void as

$body$

Declare v_query varchar;

begin

    v_query := 'insert into fortesting(cola,colb,colc,cold) values('||p_cola||','''||p_colb||''','''||p_colc||''','||p_cold||')';

    RAISE INFO '%',v_query;

    EXECUTE v_query;

end;

$body$

language plpgsql;

Calling FUNCTION:

I only pass two values to two parameter rest should go null into table.

SELECT * FROM  functionTest( p_cola := 1, p_colc:='abc');

Error Details:

INFO:  <NULL>

ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function functiontest(integer,timestamp without time zone,text,integer) line 11 at EXECUTE statement

when you compose SQL command, you have to use correct quoting functions for parameters: quote_ident or quote_literal or use USING clause. Without it the code is SQL injection vulnerable! — Pavel Stehule
It is pretty important for you or for any reader. This bug is stupid and still pretty wide. When you use syntax advised by @a_horse_with_no_name, then your code will be faster, cleaner and safe. — Pavel Stehule

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2015-12-31T10:31:08

The reason is that a concatenating a null value with something returns a null value, even if other elements are not null.

But you don't need dynamic SQL for this, you can pass the parameters directly:

insert into fortesting(cola,colb,colc,cold) values ($1, $2, $3, $4);

If you do want to use dynamic SQL (even though it's not needed) you should do the same:

v_query := 'insert into fortesting(cola,colb,colc,cold) values ($1, $2, $3, $4)';

RAISE INFO '%',v_query;

EXECUTE v_query
    using p_cola, p_colb, p_colc, p_cold;

To execute the function use:

SELECT functionTest( p_cola := 1, p_colc:='abc');

do not put it into the from clause as it does not return a "table"

PostgreSQL 9.3: Pass values to only selected parameter in function

1 Answers