psql , creating stored procedure - converting string to schema type

user_6772980 0
Viewed 49

I have a stored producedure that does this, my_schema is the name of the current schema I'm using in my db.

CREATE OR REPLACE myprocedure(data TEXT) LANGUAGE plpgsql AS $$
   DECLARE
      my_variable my_schema.my_table%ROWTYPE;
      BEGIN
          SELECT * FROM my_schema.my_table WHERE oid=3;
      END;
$$;

But, I need this to be a bit more generic, and I'd like to use this for any schema I create in my db, so, I'd like to pass in the schema name instead like this, so that I won't have to create a procedure for every schema created in the future:

note I've used angle brackets for syntax I'm not sure of:

CREATE OR REPLACE myprocedure(data TEXT, my_schema_name <SOME_TYPE>) LANGUAGE plpgsql AS $$
  DECLARE
          my_variable <my_schema_name>.my_table%ROWTYPE;
          BEGIN
              SELECT * FROM <my_schema_name>.my_table WHERE oid=3;
          END;
    $$;

and then call it like this:

CALL myprocedure('SOMETHIGN', <MY_SCHEMA_NAME>);

what is the correct syntax to do this?

postgresql
1 Answers

You can use the record type for a generic record, and you use dynamic SQL for the query. Here is an example:

CREATE PROCEDURE x(schema_name text) LANGUAGE plpgsql AS
$$DECLARE
   q record;
BEGIN
   EXECUTE format(
              'SELECT * FROM %I.data WHERE id = 1',
              schema_name
           ) INTO q;

   RAISE NOTICE '%', q.id;
END;$$;
user_64643080
Related