PostgreSQL: Getting <NULL> string by passing null value to function

Question

I have the following function with one parameter. I need to display a string which consist select statement and where condition.

Example:

create or replace function funct(a int)
returns void as
$$
declare
       wrclause varchar := '';
       sqlq varchar ;
       t varchar;
begin
       wrclause := ' AND C IN ('|| a || ')';

       sqlq := ' t :=select string_agg(''select *, abcd as "D" from '' || table_namess  ||, '' Union all '') as namess
        from tablescollection2 ud
        inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name where C = '|| a ||'' || wrclause;

       raise info '%',sqlq;

       perform sqlq;

       raise info '%',t;
end;
$$
language plpgsql;

Calling Function: With value 1

select funct(1);

Output:

INFO:   t :=select string_agg('select *, abcd as "D" from ' || table_namess ||, ' Union all ') as namess
        from tablescollection2 ud
        inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name where C = 1 AND C IN (1)
INFO:  <NULL>

Calling Function: With null value

select funct(null);

Output:

INFO:  <NULL>
INFO:  <NULL>

Note: I am not getting after assigning empty string to wrclause variable why I am getting <NULL> string and also <NULL> to variable t.

Any string concatenated with NULL will result in NULL, just as any number added to NULL will result in NULL, and any boolean expression such as AND NULL or OR NULL will evaluate to NULL. — IMSoP
– IMSoP, Commented Jul 10, 2014 at 11:09
@IMSoP, If I have null values to pass then what is the solution for this? Thanks — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Jul 10, 2014 at 11:11
@IMSoP, In SQL Server we can do this by using SET CONCAT_NULL_YIELDS_NULL OFF. How about PostgreSQL? — Sarfaraz Makandar
– Sarfaraz Makandar, Commented Jul 10, 2014 at 11:14
@Meem Rather than looking for a way to disable standard behaviour, just handle the nulls properly; if you want an empty string for null, COALESCE(var, '') is the simplest; but in this case that would result in invalid SQL anyway (AND C IN ()). — IMSoP
– IMSoP, Commented Jul 10, 2014 at 11:22

Ilesh Patel · Accepted Answer · 2014-07-10 11:27:01Z

1

You can Try something like below if it satisfied your all meets for NULL as well:

create or replace function funct(a int)
returns void as
$$
declare
       wrclause varchar := '';
       sqlq varchar ;
       t varchar;
begin
    IF (a IS NOT NULL ) THEN 
        wrclause := 'where C = '|| a ||' AND C IN ('|| a || ')';
    END IF;


       sqlq := ' t :=select string_agg(''select *, abcd as "D" from '' || table_namess  ||, '' Union all '') as namess
        from tablescollection2 ud
        inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name ' || wrclause;

       raise info '%',sqlq;

       perform sqlq;

       raise info '%',t;
end;
$$
language plpgsql;

select funct(1);

OUTPUT:

INFO:   t :=select string_agg('select *, abcd as "D" from ' || table_namess  ||, ' Union all ') as namess
        from tablescollection2 ud
        inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name where C = 1 AND C IN (1)
INFO:  <NULL>

select funct(null)

INFO:   t :=select string_agg('select *, abcd as "D" from ' || table_namess  ||, ' Union all ') as namess
        from tablescollection2 ud
        inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name
INFO:  <NULL>

answered Jul 10, 2014 at 11:27

Ilesh Patel

2,15516 silver badges27 bronze badges

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

3 Comments

Sarfaraz Makandar Over a year ago

Thank you so much. One more confusion here: what about variable t? It need to show select statement result.

Ilesh Patel Over a year ago

where is the value of variable: table_namess ?

Sarfaraz Makandar Over a year ago

That is the column name which I will get from table "tablescollection2".

Collectives™ on Stack Overflow

PostgreSQL: Getting <NULL> string by passing null value to function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related