Schema
Owner
postgres
Descriptions
Adds missing entries to topology.layer table by reading metadata from topo tables.
Options
Option | Value |
---|---|
Returns |
Table ( schema_name text, table_name text, feature_column text ) |
Language |
sql |
Parameters |
There are no parameters for function populate_topology_layer |
Definition
CREATE OR REPLACE FUNCTION topology.populate_topology_layer (
)
RETURNS TABLE (
schema_name text,
table_name text,
feature_column text
) AS
$span$
INSERT INTO topology.layer
WITH checks AS (
SELECT
n.nspname sch, r.relname tab,
replace(c.conname, 'check_topogeom_', '') col,
--c.consrc src,
regexp_matches(c.consrc,
'\.topology_id = (\d+).*\.layer_id = (\d+).*\.type = (\d+)') inf
FROM pg_constraint c, pg_class r, pg_namespace n
WHERE c.conname LIKE 'check_topogeom_%'
AND r.oid = c.conrelid
AND n.oid = r.relnamespace
), newrows AS (
SELECT inf[1]::int as topology_id,
inf[2]::int as layer_id,
sch, tab, col, inf[3]::int as feature_type --, src
FROM checks c
WHERE NOT EXISTS (
SELECT * FROM topology.layer l
WHERE l.schema_name = c.sch
AND l.table_name = c.tab
AND l.feature_column = c.col
)
)
SELECT topology_id, layer_id, sch,
tab, col, feature_type,
0, NULL
FROM newrows RETURNING schema_name,table_name,feature_column;
$span$
LANGUAGE 'sql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;
COMMENT ON FUNCTION topology.populate_topology_layer()
IS 'Adds missing entries to topology.layer table by reading metadata from topo tables.';
This file was generated with SQL Manager for PostgreSQL (www.pgsqlmanager.com) at 13/03/2014 13:23 |