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

Question

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.

[Executing Dynamic Commands](https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) — Abelisto, Jun 09 '17 at 21:17
@Abelisto Thanks, now I just have to figure out how to get around a missing quote_ident( record ) function. I hate using outdated Postgres — Evan, Jun 09 '17 at 21:53
@Abelisto I didn't try format(), but quote_ident() worked perfectly, thanks for the help. — Evan, Jun 12 '17 at 14:10

score 2 · Accepted Answer · answered Jun 10 '17 at 01:45

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.)

Table name as a PostgreSQL function parameter

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

Are PostgreSQL column names case-sensitive?

Wow, awesome answer. Didn't realize I could just skip straight to text. Thanks for the help! — Evan, Jun 12 '17 at 14:23

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

1 Answers1