[postgis-users] plpgsql - problem using variable schema and table names
Birgit Laggner
birgit.laggner at vti.bund.de
Tue Dec 22 03:58:04 PST 2009
Hi Kevin,
for the defining of n, your suggestions works perfectly well. But in the
next step, I try to assign a record as an intersection of 2 tables. I
tried to solve this with a nested loop. But the problem is, after
assigning the records for table a and b, I can't address certain fields
within the records anymore, which would be necessary for the
intersection loop.
By now, the part in question of my function looks like this (r_a, r_b
are declared as record, sql_a, sql_b are declared as text):
sql_a := 'select gid, '||a_id||' as '||a_id||', the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_a);
raise debug '%', sql_a;
raise notice '%', sql_a;
sql_b := 'select gid, '||b_id||' as '||b_id||', the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_b);
raise debug '%', sql_b;
FOR r_a in execute sql_a LOOP
FOR r_b in execute sql_b LOOP
FOR recordset_object IN execute 'select
'||quote_ident(r_a.a_id) ||',
'||quote_ident(r_b.b_id) ||',
ST_intersection('||quote_ident(r_a.the_geom)||',
'||quote_ident(r_b.the_geom)||') AS the_geom
WHERE st_intersects('||quote_ident(r_a.the_geom)||',
'||quote_ident(r_b.the_geom)||') and
'||quote_ident(r_a.the_geom)||' && '||quote_ident(r_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);';
END LOOP;
END LOOP;
END LOOP;
And the error message, I get, is:
ERROR: Record »r_a« hat no field »a_id«
Any ideas, how to handle this?
Thanks a lot and merry christmas,
Birgit.
On 17.12.2009 22:41, Kevin Neufeld wrote:
> Small typo ... it's not
> RAISE DEBUG '%s', sql;
> but
> RAISE DEBUG '%', sql;
>
> Kevin Neufeld wrote:
>> 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
>>>> <birgit.laggner at vti.bund.de <mailto:birgit.laggner at vti.bund.de>>
>>>> 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
>>>> postgis-users at postgis.refractions.net
>>>> <mailto:postgis-users at postgis.refractions.net>
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> ************************************
>>>> David William Bitner
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list