CODEWITHSUNDEEP
postgresql
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

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

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.

Upvotes: 0

Views: 1766

Answers (1)

Ilesh Patel
Ilesh Patel

Reputation: 2155

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>

Upvotes: 1

Related Questions