Birgit,
The problem may be that you are creating a varchar variable for your
schema name and then you are trying to use it in an instance that is
expecting a database object. Anytime you are trying to insert
variables as database objects, you need to construct your query as a
string and use execute similar as to how you are creating your insert
statement.
David
On Thu, Dec 17, 2009 at 9:27 AM, Birgit Laggner
<[email protected] <mailto:[email protected]>> wrote:
Dear list,
I am trying to generalize a pl/pgsql function I have written (see
below). I would like to define schema and table names, as well as
certain column names, in the function call (as in the PostGIS
function
AddGeometryColumn) in order to use them to define schema and table
names
and everything else within the function queries.
My problem is, that postgres doesn't recognize the defined variable
names if I call them in a FROM clause or INSERT INTO. This is the
error
message:
ERROR: Schema »schemaname« does not exist
LINE 1: SELECT count( $1 ) from schemaname.table_a
^
QUERY: SELECT count( $1 ) from schemaname.table_a
CONTEXT: PL/pgSQL function "_laggner_b_pgintersection" line 16 at
assignment
I can't imagine that it should be impossible to use variable
schema and
table names in a plpgsql function. So, if anybody has suggestions, I
would be quite happy.
Thanks and regards,
Birgit.
My PostGIS version: 1.4.0-10.1
My PostgreSQL version: 8.4.1-2.1
My pl/pgsql function:
CREATE OR REPLACE FUNCTION _laggner_b_pgintersection(schemaname
varchar(20), table_a varchar(50), a_id varchar(20), table_b
varchar(50),
b_id varchar(20), intersection varchar(60)) RETURNS void AS
$BODY$
DECLARE
counter integer;
recordset_object RECORD;
i integer;
n integer;
BEGIN
counter := 0;
n := count(a_id) from schemaname.table_a;
--1. Intersection:
FOR i in 1..n LOOP
RAISE NOTICE 'Beginn Intersection Tabelle 1, Polygon %', i;
FOR recordset_object IN
SELECT
a.a_id ,
b.b_id,
ST_intersection(a.the_geom, b.the_geom) AS the_geom
FROM schemaname.table_a a, schemaname.table_b b
WHERE a.a_id=i and
st_intersects(a.the_geom, b.the_geom) and
a.the_geom && b.the_geom
LOOP
execute
'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
''||b_id||'', the_geom) '||
'VALUES ( '||
''||recordset_object||'.''||a_id||'', '||
''||recordset_object||'.''||b_id||'', '||
''||recordset_object||'.the_geom);';
/*
alternatively:
INSERT INTO schemaname.intersection (a_id, b_id, the_geom)
VALUES (
recordset_object.a_id,
recordset_object.b_id,
recordset_object.the_geom);
*/
counter := counter + 1;
RAISE NOTICE 'Schreibe Intersection-Polygon %', counter ;
END LOOP;
counter := 0;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION _laggner_b_pgintersection(schemaname varchar(20),
table_a
varchar(50), a_id varchar(20), table_b varchar(50), b_id
varchar(20),
intersection varchar(60)) OWNER TO postgres;
_______________________________________________
postgis-users mailing list
[email protected]
<mailto:[email protected]>
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
************************************
David William Bitner
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
