Use variables within a function with INSERT INTO

Question

I want to use a variable within the function so i can grab systemstate from several clients and put them into a local table. It looks like this but now i am stuck with making a part variable

    CREATE OR REPLACE FUNCTION schema_test.sync_systemstate(table_name TEXT, dbname TEXT, port int, hostname TEXT, username TEXT, wachtwoord TEXT) returns integer as $$

    DECLARE
    sql varchar;

    BEGIN

    sql :='truncate schema_test.' || table_name || '' ;

    execute sql;

    INSERT INTO schema_test.[variable table name]
    SELECT timestamp, component_name, component_type, status, message ...;

    RETURN port;
    END;
    $$ LANGUAGE plpgsql;

The INSERT INTO schema_test.[variable] is not working, well at least i cant make it working. Any suggestions on how to achieve that behind the schem_test comes the variable table name??

You need dynamic SQL for that the same way you did with the truncate statement. (note that using format() is a better way of creating dynamic SQL strings) — user1822
– user1822, Commented Oct 4, 2017 at 12:47
Well... been trying to figure it out but cant seem to make it work (or understand actually..) Ill wait till the system administrator is back — Bartgast
– Bartgast, Commented Oct 4, 2017 at 13:13
Like always, your version of Postgres, please. It's essential for the question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 10, 2017 at 14:45

Evan Carroll · Accepted Answer · 2017-10-04 16:03:49Z

1

This is a bad idea. You're creating a MATERIALIZED VIEW, just use the native functionality for that.

CREATE MATERIALIZED VIEW schema_test
  AS SELECT timestamp, component_name, component_type, status, message;

Then simply run REFRESH MATERIALIZED VIEWschema_test; it will clear out the old results and insert the new results of the query.

edited Oct 4, 2017 at 16:03

answered Oct 4, 2017 at 15:44

Evan Carroll

65.8k50 gold badges263 silver badges512 bronze badges

Add a comment |

Artashes Khachatryan · Accepted Answer · 2017-10-04 14:03:43Z

0

Why you dont use dynamic sql while insering data?

CREATE OR REPLACE FUNCTION schema_test.sync_systemstate(table_name TEXT, dbname TEXT, port int, hostname TEXT, username TEXT, wachtwoord TEXT) returns integer as $$

    DECLARE
    sql varchar;

    BEGIN

    sql :='truncate schema_test.' || table_name || '' ;

    execute sql;
    sql: = 'Insert into schema_test.' || $1 || ' SELECT timestamp, component_name, component_type, status, message ...'

    execute sql;

    RETURN port;
    END;
    $$ LANGUAGE plpgsql;

answered Oct 4, 2017 at 14:03

Artashes Khachatryan

1,5331 gold badge12 silver badges23 bronze badges

2

Please make your code safer by ident-quoting your table name (or, even better, use format()).

András Váczi
– András Váczi

2017-10-04 14:55:47 +00:00
Commented Oct 4, 2017 at 14:55

Add a comment |

Stack Exchange Network

Use variables within a function with INSERT INTO

2 Answers 2

Your Answer

Hot Network Questions

Use variables within a function with INSERT INTO

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions