3

I have a lot of tables with column geom (Multipolygon) without SRID in PostgreSQL/PostGIS.

I need to set all tables with a SRID of 3003.

This is my first time with SQL function.

I'm using a part of code of this answer:

DO $$Declare r record; 
  BEGIN 
    FOR r in SELECT srid as srs, f_table_name as name, f_geometry_column as geom
     FROM geometry_columns WHERE f_table_schema = 'Schema_name'
    LOOP
       RAISE NOTICE 'updating table: %, geom column: %, with srid: %', r.name, r.geom, r.srs;
       PERFORM UpdateGeometrySRID(quote_ident(r.name), quote_ident(r.geom), 3003); 

   END LOOP;
END $$;

and the result is:

NOTIFICA:  updating table: pat4, geom column: geom, with srid: 0


ERRORE:  la funzione updategeometrysrid(name, name, integer) non esiste
LINE 1: SELECT UpdateGeometrySRID(r.name, r.geom, 3003)
               ^
HINT:  Nessuna funzione trovata con nome e tipi di argomenti forniti. Potrebbe essere necessario convertire i tipi esplicitamente.
QUERY:  SELECT UpdateGeometrySRID(r.name, r.geom, 3003)
CONTEXT:  funzione PL/pgSQL inline_code_block riga 7 a PERFORM

I don't understand because it doesn't recognise the function.

5
  • Please fix your URL reference
    – Vince
    Commented Mar 10, 2020 at 12:23
  • Sorry, now it goes Commented Mar 10, 2020 at 12:28
  • effectively without parentheses it works but It doesn't set the srid :"Query returned successfully with no result in 15 msec." Commented Mar 10, 2020 at 13:15
  • Transformed my comment into an answer; I also added my suspicion as to why it doesn't work...
    – geozelot
    Commented Mar 10, 2020 at 13:33
  • Please don't paste a solution in the question. You can post your own Answer, but it breaks the Q&A model to post the answer in the Question.
    – Vince
    Commented Mar 10, 2020 at 14:00

1 Answer 1

6

Simple answer: pass the raw string to the function!

quote_ident will format the input string into a SQL identifier, effectively wrapping it in "; UpdateGeometrySRID however, accepts plain VARCHAR strings, and runs identifier checks internally!


However:

You may need to specify the schema in UpdateGeometrySRID:

DO $$
  DECLARE
    r record; 
  BEGIN 
    FOR r in (
      SELECT srid AS srs,
             f_table_name AS "name",
             f_table_schema AS "schema",
             f_geometry_column AS geom
      FROM   geometry_columns
      WHERE  f_table_schema = 'Schema_name'
    ) LOOP
       RAISE NOTICE 'updating table: %.%, geom column: %, with srid: %', r."schema", r."name", r.geom, r.srs;
       PERFORM UpdateGeometrySRID(r."schema", r."name", r.geom, 3003); 
    END LOOP;
  END;
$$

Without an explicit schema given, the function will call current_schema() to fill in, which defaults to public!

6
  • Perfect!!! I specified the scheme. Thank you very much. I add the solution in my post. Commented Mar 10, 2020 at 13:44
  • @DanielePiccolo ah scusi, I actually forgot to include what I proposed into the function I copied in ;-) it's updated now
    – geozelot
    Commented Mar 10, 2020 at 13:44
  • nice - the rare Postgres DO block
    – ziggy
    Commented Mar 10, 2020 at 13:48
  • This yielded a syntax error in my case with wrong argument types, it worked after using quote_ident around the UpdateGeometrySRID function, e.g. ... PERFORM UpdateGeometrySRID(quote_ident(r."name"), quote_ident(r."geom"), 25832); ... Commented May 6, 2020 at 6:05
  • @TimothyDalton well, that's exactly the opposite of what I proposed ,) do any of your tables have "-wrapped names? I may need to investigate...
    – geozelot
    Commented May 6, 2020 at 6:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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