PostgreSQL – Using COPY Query with Variable Path

plpgsqlpostgresql

I am trying to write a stored procedure that imports a file but what I've noticed is that COPY can be followed by string literal and no variable?

I am trying to achieve something like this

FUNCTION insert_file (IN filepath VARCHAR(255)) ... 
   COPY filepath ...

is it even possible or I need to do some magic?

Best Answer

Yes, for example:

FUNCTION insert_file (IN filepath VARCHAR(255)) ... 
sqltxt:='COPY table_name from '''||filepath||''' with (FORMAT csv)';
execute sqltxt;

'''||variable||''' will produce 'value' with quotation marks.

'||variable||' will produce the value without quotation marks. usefully for table names and integers for example.

You need to declare sqltxt as text.

Related Solutions

Postgresql – Untyped functions that return tables

This is a misunderstanding. A function like this is perfectly valid:

CREATE OR REPLACE FUNCTION test()
 RETURNS SETOF record AS
$func$
VALUES (1, 2), (3, 4);  -- 2 rows with 2 integer columns
$func$ LANGUAGE sql;

However, since it returns anonymous records, you are required to provide a column definition list with every call , just like the error message told you. SQL demands to know what to expect from a function up front. The call would work like this:

SELECT * FROM test() AS f(a int, b int);

Functions returning anonymous records have their uses. I try to avoid them mostly, because the call is rather unwieldy. Depending on your exact requirements, there are a couple of ways around it. I wrote a comprehensive answer for that on SO recently.

PostgreSQL – Cannot Create RULE from Stored Procedure

Thank you everyone for suggestions in comments. I've found out two solutions.

Use SQL stored procedure instead of PL/pgSQL:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS VOID AS $test_create_rule_from_stored_procedure$
    CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
    DO INSTEAD
      INSERT INTO person_y (
        firstname,
        lastname)
      VALUES (
        new.firstname,
        new.lastname
      );
    $test_create_rule_from_stored_procedure$ LANGUAGE SQL;

Stay with PL/pgSQL but specify query for creating rule as string literal:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
    BEGIN
      EXECUTE 'CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
      DO INSTEAD
        INSERT INTO person_y (
          firstname,
          lastname)
        VALUES (
          new.firstname,
          new.lastname
        );';

      RETURN 1;
    END;
    $test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;

Best Answer

Related Solutions

Postgresql – Untyped functions that return tables

PostgreSQL – Cannot Create RULE from Stored Procedure

Related Question