CODEWITHSUNDEEP
sqlpostgresqlsql-insertpostgresql-9.3execute
user565447
user565447

Reputation: 999

INSERT EXECUTE FORMAT Postgresql string

I want to create a function which will update the table.

I am trying to run it:

SELECT insert_function('asda', 1 ,1 , 'asd', 1)

But I get the error:

LINE 3 VALUES("asda","1","1","asd","1") column doesn't exist.

When I am trying rot run:

SELECT insert_function('1', 1 ,1 , '1', 1) zero-length delimited identifier at near """""""

LINE 3 VALUES(""1"","1","1",""1"","1") (^at the first item)

CREATE TABLE IF NOT EXISTS commits (
    id SERIAL PRIMARY KEY, 
    goo CHAR(64) NOT NULL, 
    foo INTEGER NOT NULL, 
    bla INTEGER NOT NULL, 
    ma CHAR(512) NOT NULL, 
    fgt INTEGER NOT NULL
);

CREATE OR REPLACE FUNCTION insert_function(goo char(64), foo INTEGER, bla INTEGER, ma CHAR(512), fgt INTEGER)
  RETURNS VOID AS
$func$
BEGIN
EXECUTE format('
   INSERT INTO commits 
   VALUES(%I,  %I,  %I,  %I, %I)', 
   goo , foo , bla , ma , fgt );

END
$func$ LANGUAGE plpgsql;

Could you please tell me how can I insert values? How can I write the function with EXECUTE format?

Upvotes: 8

Views: 13339

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Specify column names and use using:

CREATE OR REPLACE FUNCTION insert_function(goo char(64), foo INTEGER,
                                           bla INTEGER, ma CHAR(512), fgt INTEGER)
RETURNS VOID AS
$func$
BEGIN
EXECUTE format('INSERT INTO commits(goo, foo, bla, ma, fgt)
                VALUES($1,$2,$3,$4,$5);') using goo, foo, bla, ma, fgt;

END
$func$ LANGUAGE plpgsql;

SqlFiddleDemo

Upvotes: 8

Roberto Damian Alfonso
Roberto Damian Alfonso

Reputation: 638

The %I (uppercase i) formatter escapes the argument as an SQL identifier (docs here), which is not what you want. You should use %s in this case. 

EXECUTE format('INSERT INTO commits VALUES(%L,  %s,  %s,  %L, %s)', goo , foo , bla , ma , fgt );

Upvotes: 2

Houari
Houari

Reputation: 5621

You have two problems:

When you want to insert into table, you have to specify all columns (even auto calculated columns), or if you don't want to specify all the columns names, you have to specify all of column value, even auto calcluated value too.

Concerning the format of your dynamic query, you should use %s instead of %I to interpolates the corresponding argument as a string which you want to achieve, and the %I escapes its argument as an SQL identifier which you don't want to.

So you can resolve your problem by replacing:

EXECUTE format('
   INSERT INTO commits 
   VALUES(%I,  %I,  %I,  %I, %I)', 
   goo , foo , bla , ma , fgt );

By

EXECUTE format('
   INSERT INTO commits 
   VALUES(DEFAULT, %L ,  %s ,  %s ,  %L , %s )', 
   goo , foo , bla , ma , fgt );

Upvotes: 5

Related Questions