Thread: passing column name to a PL/pgsql function for ALTER TABLE ADD

Hello.

Is it possible? I would like to do something like
CREATE OR REPLACE FUNCTION add_column(name, anyelement) RETURNS integer AS '
  DECLARE
    col_name ALIAS FOR $1;
    def_val  ALIAS FOR $2;
  BEGIN
    ALTER TABLE my_table ADD col_name def_val%TYPE;
    RETURN 0;
  END;
' LANGUAGE plpgsql;
SELECT add_column('a', 1);

Thanks,
--
Alexander Kotelnikov
Saint-Petersburg, Russia

On Thu, Aug 11, 2005 at 02:50:11PM +0400, Alexander Kotelnikov wrote:
>
> Is it possible? I would like to do something like
> CREATE OR REPLACE FUNCTION add_column(name, anyelement) RETURNS integer AS '
>   DECLARE
>     col_name ALIAS FOR $1;
>     def_val  ALIAS FOR $2;
>   BEGIN
>     ALTER TABLE my_table ADD col_name def_val%TYPE;
>     RETURN 0;
>   END;
> ' LANGUAGE plpgsql;
> SELECT add_column('a', 1);

Do you really need this function to be polymorphic, or can you pass
the new column's type as a text argument?  In any case, you'll
probably want to read "Executing Dynamic Commands" in the PL/pgSQL
documentation:

http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Michael Fuhr