How to use dynamic column name in PostgreSQL

Question

I want use dynamic column names in my query, so I use execute format. But my query does not work. This is my query...

create or replace function container_type() returns table (contType text) as 
$func$ 
declare 
    containerType text[] := '{d20, r20, h20, rh20, e20, d40, r40, h40, rhc, e40, 45, tank, tk40, tk45, e45, r45, rh45, h45}';
    column01 text;
    column02 text;
begin
    FOR i IN 1 .. array_upper(containerType, 1)
    loop
    column01 = upper(containerType[i]);
    column02 = concat('"booking_', containerType[i], '"');

    execute
    format(
        'insert into tmp_booking_containers (
            booking_data_source,
            booking_place_of_creation,
            booking_prefix,
            booking_number,
            booking_prefix_number,
            booking_container_type,
            booking_container_quantity
        ) select 
            booking_data_source,
            booking_place_of_creation,
            booking_prefix,
            booking_number,
            booking_prefix_number,
            $1,
            $2
        from dim_booking_masters 
        where $2 != 0'
    ) using column01, column02;

    end loop;
end
$func$ LANGUAGE plpgsql STABLE;

This is the error shown when I run my function:

enter image description here

Laurenz Albe · Accepted Answer · 2019-02-26 07:57:20Z

1

You are mixing up format and the USING clause of EXECUTE.

format replaces occurrences of %I, %L and %s (danger! no escaping!) in its first argument with the following arguments (cast to text).

You use it if you need to interpolate things like table and column names.

The USING clause of EXECUTE is to specify arguments for parameters, like in prepared statements. The arguments are bound to the $1, $2 (and so on) placeholders. You can only use parameters where you could also use a constant of some type in SQL.

Both techniques protect you against SQL injection.

Your code should look like this:

EXECUTE format(
            'insert into tmp_booking_containers (
                booking_data_source,
                booking_place_of_creation,
                booking_prefix,
                booking_number,
                booking_prefix_number,
                booking_container_type,
                booking_container_quantity
            ) select 
                booking_data_source,
                booking_place_of_creation,
                booking_prefix,
                booking_number,
                booking_prefix_number,
                %I,
                %I
            from dim_booking_masters 
            where %I != 0',
            column01, column2, column02;

answered Feb 26, 2019 at 7:57

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

Sukma Saputra Over a year ago

Thanks for the answer, now I have fix my query. But I got new error like this..."SQL Error [42601]: ERROR: INSERT has more target columns than expressions Where: PL/pgSQL function container_type() line 15 at EXECUTE". I think the error come because I use loop. Thanks

Laurenz Albe Over a year ago

No, that error means that you have something like INSERT INTO tab (col1, col2, col3) VALUES (1, 2). Seems like an unrelated problem.

Collectives™ on Stack Overflow

How to use dynamic column name in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related