0

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

  • 1
    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) – a_horse_with_no_name Oct 4 '17 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 Oct 4 '17 at 13:13
  • Like always, your version of Postgres, please. It's essential for the question. – Erwin Brandstetter Oct 10 '17 at 14:45
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.

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;
  • 2
    Please make your code safer by ident-quoting your table name (or, even better, use format()). – dezso Oct 4 '17 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.