http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
Try:
FOR target IN EXECUTE text_expression LOOP
...
END LOOP;
I usually do something like this:
DECLARE
...
schemaname text := 'myschema';
tablename text := 'mytable';
sql text;
r record;
BEGIN;
sql := 'SELECT ... FROM ' || quote_ident(schemaname) || '.' ||
quote_ident(tablename);
RAISE DEBUG '%s', sql;
FOR r IN EXECUTE sql LOOP
...
END LOOP
END;
-- Kevin
Birgit Laggner wrote:
Yes, I have noticed that, but I don't know how to do that, especially at
defining my n (loop end point variable). I tried various versions (also
with execute), but without success.
Birgit.
On 17.12.2009 16:38, David William Bitner wrote:
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
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
