CODEWITHSUNDEEP
postgresqlpartitioning
Manan Shah
Manan Shah

Reputation: 1098

can not insert row in a table while data redirecting from parent table to child table in trigger(stored procedure)

I am using postgres version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

Description:

I have one parent table and that parent table has many child tables. I want to redirect the data from parent table to child table (while insertion)

(same As this link: Example Link ) (example of ice cream company)

I did following things...

create table tsttbl1 (id integer, name text);

create table tsttbl1_hour0 (LIKE tsttbl1) INHERITS (tsttbl1);

CREATE OR REPLACE FUNCTION insert_tsttbl1_hourbase() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Hi...%', NEW;
    execute 'INSERT INTO ' || TG_TABLE_SCHEMA|| '.tsttbl1_hour0 values '|| NEW;
    -- INSERT INTO tsttbl1_hour0 values (NEW.*)';
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

create trigger trigger_insert_tsttbl1_hourbase before insert on tsttbl1 FOR EACH ROW EXECUTE PROCEDURE insert_tsttbl1_hourbase();

Here,

parent table:- tsttbl1

child table:- tsttable1_hour0

Trying to achieve: insert into tsttbl1 (id, name) values (0,'xyz'); should insert data in tsttable1_hour0 instead of tsttbl1;

But When i tried to insert the row, i am getting like this...

ccc=# insert into tsttbl1 (id,name) values (1, 'abc');
LOG:  statement: insert into tsttbl1 (id,name) values (1, 'abc');
NOTICE:  Hi...(1,abc)
ERROR:  column "abc" does not exist
LINE 1: INSERT INTO act1.tsttbl1_hour0 values (1,abc)
                                                 ^
QUERY:  INSERT INTO act1.tsttbl1_hour0 values (1,abc)
CONTEXT:  PL/pgSQL function insert_tsttbl1_hourbase() line 4 at EXECUTE statement

Additional Information:

-> This data redirection can also be achieved by rule. But for my case, this insertion is frequently, so rule might be costly compare to trigger...

so i want to achieve this by trigger only.

-> if i un comment the line "INSERT INTO tsttbl1_hour0 values (NEW.*)'" .. row is inserted successfully

-> if i un comment the line "INSERT INTO tsttbl1_hour0 values (NEW.id, NEW.name)'" .. row is inserted successfully

Upvotes: 0

Views: 567

Answers (2)

Manan Shah
Manan Shah

Reputation: 1098

My problem is solved in this way...

CREATE OR REPLACE FUNCTION insert_tsttbl1_hourbase() RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Hi...%', NEW;
    Execute 'set search_path to ' || TG_TABLE_SCHEMA|| '';
    INSERT INTO tsttbl1_hour0 values (NEW.*);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

instead of schemaname.tablename, i have set the search path before of the statement.

Upvotes: 0

Glenn
Glenn

Reputation: 9160

The execute command is invoking dynamic sql. It will execute the string that follows, but the syntax pasted together is not correct. It should be:

execute 'INSERT INTO ' || TG_TABLE_SCHEMA|| '.tsttbl1_hour0 values (' || NEW.id || ', ''' || NEW.name || ''')';

That would get that line working, but rather than pasting the line together, with values, binding would be better. See the PREPARE command.

But why use dynamic sql at all rather than the commented line that runs successfully?

Upvotes: 1

Related Questions