Postgresql – Use variables within a function with INSERT INTO

plpgsqlpostgresql

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??

Best Answer

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.

Related Question