Is there any way to set a style from public.layer_styles table to a postgis table while creation?

  • 1
    What code are you using to create the table? I think that should be part of your very brief question. – PolyGeo Jun 28 '17 at 9:30
  • are you using QGIS or ArcGIS? – LaughU Jun 28 '17 at 9:31
  • I am creating using pgSQL – User123 Jun 28 '17 at 9:32
  • @LaughU: i have done it using QGIS but need to do it dynamically during table creation using pgSQL – User123 Jun 28 '17 at 9:33
  • This can be done with an event trigger in Postgres. If @PolyGeo wants to re-open this, I can provide an answer. – dbaston Jul 1 '17 at 18:53

Recent version of Postgres let you perform actions automatically after data definition language (DDL) changes such as CREATE TABLE using event triggers. Here's a skeleton for an event trigger that will insert appropriate data into layer_styles every time a spatial table is created.

First, we create an event trigger function. It's similar to a regular trigger but has access to different variables and functions, reflecting the special context in which it's executed. In this case we use the pg_event_trigger_ddl_commands function to get the name of the created table.

CREATE OR REPLACE FUNCTION create_layer_style()
RETURNS event_trigger AS $$
  table_catalog   text;
  table_schema    text;
  table_name      text;
  geometry_column text;
  tbl_id          oid;

table_catalog := current_database();
table_schema := (pg_event_trigger_ddl_commands()).schema_name;
tbl_id := (pg_event_trigger_ddl_commands()).objid;
EXECUTE 'SELECT relname FROM pg_class WHERE oid=$1' USING tbl_id INTO table_name;

FOR geometry_column IN EXECUTE 'SELECT f_geometry_column FROM public.geometry_columns WHERE f_table_catalog=$1 AND f_table_schema=$2 AND f_table_name=$3' 
  USING table_catalog, table_schema, table_name LOOP
    -- INSERT INTO public.layer_styles
    -- (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, ...)
    -- VALUES
    -- (table_catalog, table_schema, table_name, geometry_column, ...)

$$ LANGUAGE plpgsql;

Then, we bind the trigger to all CREATE TABLE events in the database:

  ON ddl_command_end
EXECUTE PROCEDURE create_layer_style();

To use this, you'd need to update the commented section of the trigger function to specify what data should be added to the remainder of the columns in layer_styles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.