Dealing with Schemas and parameterizing plpgsql functions

The easiest way is https://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349

Using the regclass

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;


From https://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II

Quote_ident for schema.name

Using single quotes is one protection against SQL injection. The function quote_ident checks its argument and, when the argument contains problematic chars, it returns the argument surrounded by single quotes. It’s quite easy and useful. Problem is with schema.name pair, because the dot is a watched char:

postgres=# select quote_ident('public.foo');
 quote_ident  
--------------
 "public.foo"
(1 row)

postgres=# select * from "public.foo";
ERROR:  relation "public.foo" does not exist
postgres=# select * from public."foo";
 a 
---
(0 rows)

postgres=# select * from "public"."foo";
 a 
---
(0 rows)

We can get around this limitation with custom functions:

CREATE OR REPLACE FUNCTION quote_array(text[]) 
RETURNS text AS $$
SELECT array_to_string(array(SELECT quote_ident($1[i]) 
                                FROM generate_series(1, array_upper($1,1)) g(i)),
                       '.') 
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION quote_schema_ident(text) 
RETURNS text AS $$
SELECT quote_array(string_to_array($1,'.'))
$$ LANGUAGE SQL IMMUTABLE;

postgres=# select quote_schema_ident('public.foo tab');
 quote_schema_ident 
--------------------
 public."foo tab"
(1 row)


What I was trying at first

            split_schema text;
            split_table text;
            test text;

            begin
	        -- split it into schema and table    
	        select into split_schema split_part(table_name, '.', 1);   
	        select into split_table split_part(table_name, '.', 2); 
	        call set_config('search_path', split_schema, true);

Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s