CODEWITHSUNDEEP
sqlstringpostgresqlpostgresql-9.3
MAK
MAK

Reputation: 7260

PostgreSQL 9.3: Prepare dynamic string

I want to prepare a dynamic string using the function.

There are two main stings in this: one is passing to the function and other one is present in the function. The output string will be changes according to the passing string to the function.

Example:

I have string string1 which will pass to the the function. And string2 is present in the function.

One string that is string2 is present in the function:

string2 = 'A1,A2,A3,A4'

And string that is string1 I am passing to the function:

string1 = 'A1'

Then the expected prepared string should lookes like:

A1 = 1 AND A2 IS NULL AND A3 IS NULL AND A4 IS NULL

For the above result I have written the folling function:

CREATE OR REPLACE FUNCTION f_test(string1 varchar)
RETURNS VOID AS
$$
DECLARE
    string2 varchar = 'A1,A2,A3,A4';
    string3 varchar;
    string4 varchar;
    string5 varchar;
    string6 varchar;
BEGIN
    string3 := REPLACE(string1,',',' = 1 AND ')||' = 1';

    RAISE INFO '%',string3;

    string4 := REPLACE(string2,string1,string3);

    RAISE INFO '%',string4; 

    string5 := REPLACE(string4,'1,',' 1 AND ');

    string6 := REPLACE(string5,',', ' IS NULL AND ')||' IS NULL ';

    RAISE INFO '%',string6; 
END;
$$
LANGUAGE PLPGSQL;

Calling FUNCTION

SELECT f_test('A1');

Result:(Right)

A1 =  1 AND A2 IS NULL AND A3 IS NULL AND A4 IS NULL

But got stuck when pass A4 

SELECT f_test('A4');

Result:(Wrong)

A 1 AND A2 IS NULL AND A3 IS NULL AND A4 =  1

While I was expecting: 

A1 IS NULL AND A2 IS NULL AND A3 IS NULL AND A4 =  1

If I call:

SELECT f_test('A2,A4');

Then result should be:

A1 IS NULL AND A2 = 1 AND A3 IS NULL AND A4 =  1

Upvotes: 0

Views: 128

Answers (1)

Vivek S.
Vivek S.

Reputation: 21905

Try this

CREATE OR REPLACE FUNCTION f_test(string1 varchar)
RETURNS VOID AS
$$
DECLARE
    string2 varchar = 'A1,A2,A3,A4,A5,A6,A7';
    string3 varchar;
    string4 varchar;
    string5 varchar;
    string6 varchar;
    intCount int;
BEGIN
    string3 := REPLACE(string1,',',' = 1 AND ')||' = 1';

    RAISE INFO '%',string3;

    string4 := REPLACE(string2,string1,string3);

    RAISE INFO '%',string4; 
select string_agg(c,' AND ') into  string6 from (
select  * from (
select c ||'= 1' c from (
select regexp_split_to_table(string2,',') c
)t 
where c in (select regexp_split_to_table(string1,','))
union all 
select c ||' IS NULL ' c from (
select regexp_split_to_table(string2,',') c
)t 
where  c  not in (select regexp_split_to_table(string1,','))
) t group by c order by c 
)t;
    RAISE INFO '%',string6; 
END;
$$
LANGUAGE PLPGSQL;

Call : - select f_test('A3,A5') or select f_test('A3,A5,A2,A6')

Upvotes: 1

Related Questions