assign_vertex_id()

[antonpa]

hi friends my english language is poor.i want to use assign_vertex_id() with SCHEMA name. for example i need use: select assign_vertex_id('baharestan.roads', 0.001, 'the_geom', 'gid'); but it has this error:

NOTICE: CREATE TABLE will create implicit sequence "vertices_tmp_id_seq" for serial column "vertices_tmp.id" CONTEXT: SQL statement "CREATE TABLE vertices_tmp (id serial)" PL/pgSQL function "assign_vertex_id" line 21 at EXECUTE statement ERROR: relation "baharestan.roads" does not exist CONTEXT: SQL statement "SELECT count(*) as countids FROM "baharestan.roads"" PL/pgSQL function "assign_vertex_id" line 28 at FOR over EXECUTE statement

thanks a lot

[antonpa]

I have the same problem. "assign_vertex_id" works if I run it on a table of roads (made from Tiger 2009 shapefiles) that was created in the public schema, but if I create it in any other schema, I get that error he listed, whether I qualify the table name with the schema prefix or not.

[justjkk]

I modified assign_vertex_id to accept an optional schema name. I have a doubt regarding which schema 'vertices_tmp' table should be created in. Check this branch: https://github.com/pgRouting/pgrouting/commits/issue16

[woodbri]

I don't see a need for this. you can change your search path before you run the command. Or you can write a trivial wrapper the changes the search path then execute the original command. I don't think we want to add schema to all our commands, and I can't see any overriding reason that this should get it and the others should not.

[dkastl]

I agree with Steve as if we add "schema" here we have to add it everywhere.
Better would be that when a function takes a table name as parameter, then this parameter should also work as schema.table.

[woodbri]

This is a little bit problematic from the point of view that we should be passing table name through quote_ident(tablename) which results in:

pgr_test=# select quote_ident('schema.table');
  quote_ident
---------------
 "schema.table"

which is NOT what we want. What we need is "schema"."table". We could write a utility function like
pgr_quote_ident() that splits the string on the '.' and passes each of the components through qoute_ident() and reassembles the string. Then change of the code to use that.

[dkastl]

This utility function sounds like a good idea to me.

[woodbri]

pgr_quote_ident(text) will get pushed in a minute. I used this name instead of camelcase so it mirror the postgresql function.

[dkastl]

Cool. Thanks!