schemas in functions

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-novice(at)postgresql(dot)org
Subject: schemas in functions
Date: 2006-02-03 09:48:45
Message-ID: 43E326FD.7090102@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message A. Kretschmer 2006-02-03 10:03:42 Re: schemas in functions
Previous Message Tom Lane 2006-02-03 04:57:08 Re: function privileges