CODEWITHSUNDEEP
postgresqlloopsplpgsqldynamic-sqlddl
Evan
Evan

Reputation: 9

Loop on rows produced by SELECT - looping variable does not exist?

The function is created fine, but when I try to execute it, I get this error:

ERROR: relation "column1" does not exist
SQL state: 42P01
Context: SQL statement "ALTER TABLE COLUMN1 ADD COLUMN locationZM geography (POINTZM, 4326)"
PL/pgSQL function addlocationzm() line 6 at SQL statement

Code:

CREATE OR REPLACE FUNCTION addlocationZM() 
RETURNS void AS
$$
    DECLARE
        COLUMN1 RECORD;
    BEGIN
        FOR COLUMN1 IN SELECT f_table_name FROM *schema*.geography_columns WHERE type LIKE 'Point%' LOOP
            ALTER TABLE COLUMN1 ADD COLUMN locationZM geography (POINTZM, 4326); 
        END LOOP;
    END;
$$
LANGUAGE 'plpgsql';

SELECT addlocationZM()

I'm probably just being dumb, but I've been at this for a while now and I just can't get it. The SELECT f_table_name ... statement executed on its own returns 58 rows of a single column, each of which is the name of a table in my schema. The idea of this is to create a new column, type PointZM, in each table pulled by the SELECT.

Upvotes: 0

Views: 6502

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658032

The function would work like this:

CREATE OR REPLACE FUNCTION addlocationZM() 
  RETURNS void AS
$func$
DECLARE
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT f_table_name FROM myschema.geography_columns WHERE type LIKE 'Point%'
   LOOP
      EXECUTE
      format('ALTER TABLE %I ADD COLUMN location_zm geography(POINTZM, 4326)', _tbl); 
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Note how I use a simple text variable to simplify matters. You don't need the record to begin with.

If it's a one-time operation, use a DO command instead of creating a function:

DO
$do$
BEGIN
   EXECUTE (
      SELECT string_agg(
                format(
                   'ALTER TABLE %I ADD COLUMN location_zm geography(POINTZM, 4326);'
                  , f_table_name)
              , E'\n')
      FROM   myschema.geography_columns
      WHERE  type LIKE 'Point%'
      );
END
$do$;

This is concatenating a single string comprised of all commands (separated with ;) for a single EXECUTE.

Or, especially while you are not familiar with plpgsql and dynamic SQL, just generate the commands, copy/paste the result and execute as 2nd step:

SELECT 'ALTER TABLE '
     || quote_ident(f_table_name)
     || ' ADD COLUMN locationZM geography(POINTZM, 4326);'
FROM   myschema.geography_columns
WHERE  type LIKE 'Point%';

(Demonstrating quote_ident() this time.)

Related:

Aside: Unquoted CaMeL-case identifiers like locationZM or your function name addlocationZM may not be such a good idea:

Upvotes: 2

Related Questions