Pass schema name as a variable in postgresql query

Question

I have a table with schema names. I am writing a procedure to select schema name from the table and into a variable. The variable is then used to fetch records from the schema table. Sample code below.

CREATE OR REPLACE PROCEDURE vasmol_master.sp_pushmt(
    )
LANGUAGE 'plpgsql'
AS $BODY$

DECLARE
    shortcodedatabase CHARACTER VARYING;
    services CHARACTER VARYING;
    BEGIN
         FOR shortcodedatabase IN SELECT dbase FROM vasmol_master.shortcode_services ORDER BY shortcode ASC LOOP
            services := shortcodedatabase ||'.smsservices';
            
            SELECT * FROM services;
        END LOOP;
    END

$BODY$;

From the answer I might have an idea what the question is, but I don't know what the problem is. Can you please add detail for both of these? — RianLauw
– RianLauw, Commented May 25, 2022 at 19:01

hgb · Accepted Answer · 2022-03-09 19:59:24Z

4

As the documentation linked to in a comment by Richard Huxton explains, to substitute a variable identifier into a query, use EXECUTE. See the documentation.

Depending on the return values you want, your procedure could look something like the following. This uses format to create a query string, substituting in the schema name as an identifier (%I).

CREATE OR REPLACE PROCEDURE vasmol_master.sp_pushmt()
  LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
  shortcodedatabase CHARACTER VARYING;
BEGIN
  FOR shortcodedatabase IN
    SELECT dbase
    FROM vasmol_master.shortcode_services
    ORDER BY shortcode ASC
  LOOP
    EXECUTE format('SELECT * FROM %I.smsservices', shortcodedatabase);
  END LOOP;
END
$BODY$;

edited Mar 9, 2022 at 19:59

answered Mar 9, 2022 at 19:17

hgb

1413 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pass schema name as a variable in postgresql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related