2

I'm trying to add a field to a table dynamically. I created a function and pass field name and datatype that I would like to create:

CREATE OR REPLACE function "trustedforms2"."parametertest"("_pname" varchar)
 AS $BODY$BEGIN
    -- Routine body goes here...
ALTER TABLE byoung.formdata ADD COLUMN _pname varchar(255); 

END$BODY$
  LANGUAGE plpgsql

This always creates a field named "_pname" regardless of the value I pass. How do I get the function to evaluate the variable by value and not as a literal? I tried "_pname", '_pname', %1 %_pname and either get an error or a field called "_pname".

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
spar cusa
  • 43
  • 3
  • 1
    You need [dynamic SQL](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) –  Aug 28 '20 at 22:09

2 Answers2

1

When using dynamic SQL, be sure to quote / escape user input that's converted to code properly to avoid attack vectors for SQL injection! Basics:

Also, Postgres identifiers only preserve capitalization when double-quoted:

Basic safe version of your routine:

CREATE OR REPLACE PROCEDURE trustedforms2.parametertest(_pname text)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   EXECUTE format('ALTER TABLE byoung.formdata ADD COLUMN %I varchar(255)', _pname); 
END
$proc$;

Capitalization is preserved this way. To force lower-case names, use lower(_pname) instead. (You still want to double-quote the identifier properly!)

You may or may not want to check first whether the table exists, and whether the column doesn't. The simple form raises an exception if those assumptions don't hold.

You can use a PROCEDURE for this like you chose in your answer, but remember to execute it with CALL:

CALL trustedforms2.parametertest('my_columns_name');

I would probably stick to a (more common) function.

And the type varchar(255)typically is a misunderstanding in Postgres:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

oK, solved

CREATE OR REPLACE PROCEDURE "trustedforms2"."parametertest"("_pname" varchar)
 AS $BODY$BEGIN
EXECUTE 'ALTER TABLE byoung.formdata ADD COLUMN ' || _pname || ' varchar(255)' ; 

END$BODY$
  LANGUAGE plpgsql
sticky bit
  • 36,626
  • 12
  • 31
  • 42
spar cusa
  • 43
  • 3