Thread: schemas in functions

schemas in functions

From
Arnaud Lesauvage
Date:
Hi list !

I am struggeling to get a function to work.
The purpose of this function is to be able to configure a schema
with different kinds of tables and functions whenever a new user
us created.
This schema is a 'user' schema : there is one for every user, but
they don't have all the same configurations.

I created a procedure like this :
CREATE OR REPLACE FUNCTION configure_schema(varchar) RETURNS VOID
AS $function$
BEGIN
SET search_path TO $1;
CREATE TABLE blablabla [...];
SELECT AddGeometryColumn($1,'table','my_geometry',12345,'GEOMETRY',2);
[...]
END;
$function$ LANGUAGE plpgsql;

So I want to be able to call this function with the schema name as
a parameter, so that all underlying queries are run in the
appropriate schema.
First of all, I have an error at 'SET search_path TO $1;' near $1.
I don't know how I should write this in a function ?
Then, I really need to pass the schema name as a parameter,
because the PostGIS function AddGeometryColumn needs the schema
name as the first parameter.

What is the correct way to achieve this ?

Thanks a lot !
--
Arnaud


Re: schemas in functions

From
"A. Kretschmer"
Date:
am  03.02.2006, um 10:48:45 +0100 mailte Arnaud Lesauvage folgendes:
> So I want to be able to call this function with the schema name as a
> parameter, so that all underlying queries are run in the appropriate
> schema.

Please read:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===