Multiple ALTER TABLE ADD COLUMN in one SQL function call

Question

I came across some weird behaviour I'd like to understand.

I create a plpgsql function doing nothing except of ALTER TABLE ADD COLUMN. I call it 2 times on the same table:

A) In a single SELECT sentence
B) In a SQL function with same SELECT as in A)

Results are different: A) creates two columns, while B) creates only one column. Why?

Code:

CREATE FUNCTION add_text_column(table_name text, column_name text) RETURNS VOID
LANGUAGE plpgsql
AS $fff$
BEGIN
    EXECUTE '
        ALTER TABLE ' || table_name  || '
        ADD COLUMN  ' || column_name || ' text;
    ';
END;
$fff$
;
--  this function is called only in B
CREATE FUNCTION add_many_text_columns(table_name text) RETURNS VOID
LANGUAGE SQL
AS $fff$
    WITH
    col_names (col_name) AS (
        VALUES
            ( 'col_1' ),
            ( 'col_2' )
    )
    SELECT  add_text_column(table_name, col_name)
    FROM    col_names
    ;
$fff$
;

-- A)
CREATE TABLE a (id integer);

WITH
col_names (col_name) AS (
    VALUES
        ( 'col_1' ),
        ( 'col_2' )
)
SELECT  add_text_column('a', col_name)
FROM    col_names
;
SELECT * FROM a;

-- B)
CREATE TABLE b (id integer);
SELECT add_many_text_columns('b');
SELECT * FROM b;

Result:

CREATE FUNCTION
CREATE FUNCTION
CREATE TABLE
 add_text_column
-----------------


(2 rows)

 id | col_1 | col_2
----+-------+-------
(0 rows)

CREATE TABLE
 add_many_text_columns
-----------------------

(1 row)

 id | col_1
----+-------
(0 rows)

I'm using PostgreSQL 10.4. Please note that this is only a minimal working example, not the full functionality I need.

404 · Accepted Answer · 2018-06-12 10:25:09Z

CREATE OR REPLACE FUNCTION g(i INTEGER)
RETURNS VOID AS $$
BEGIN
        RAISE NOTICE 'g called with %', i;
END 
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS VOID AS $$
        SELECT g(id)
        FROM generate_series(1, i) id;
$$ LANGUAGE SQL;

What do you think happens when I run SELECT t(4)? The only statement printed from g() is g called with 1.

The reason for this is your add_many_text_columns function returns a single result (void). Because it's SQL and is simply returning the result of a SELECT statement, it seems to stop executing after getting the first result, which makes sense if you think of it - it can only return one result after all.

Now change the function to:

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS SETOF VOID AS $$
        SELECT g(id)
        FROM generate_series(1, i) id;
$$ LANGUAGE SQL;

And run SELECT t(4) again, and now this is printed:

g called with 1
g called with 2
g called with 3
g called with 4

Because the function now returns SETOF VOID, it doesn't stop after the first result and executes it fully.

So back to your functions, you could change your SQL function to return SETOF VOID, but it doesn't really make much sense - better I think to change it to plpgsql and have it do a PERFORM:

CREATE OR REPLACE FUNCTION t(i INTEGER)
RETURNS VOID AS $$
BEGIN
        PERFORM g(id)
        FROM generate_series(1, i) id;
END
$$ LANGUAGE plpgsql;

That will execute the statement fully and it still returns a single VOID.

Erwin Brandstetter · Accepted Answer · 2018-06-12 23:05:53Z

1

eurotrash provided a good explanation.

Alternative solution 1

CREATE OR REPLACE FUNCTION t(i INTEGER)
  RETURNS VOID AS
$func$
   SELECT g(id)
   FROM generate_series(1, i) id;

   SELECT null::void;
$func$  LANGUAGE sql;

Because, quoting the manual:

SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned.

By adding a dummy SELECT at the end we avoid that Postgres stops after processing the the first row of the query with multiple rows.

Alternative solution 2

CREATE OR REPLACE FUNCTION t(i INTEGER)
  RETURNS VOID AS
$func$
   SELECT count(g(id))
   FROM generate_series(1, i) id;
$func$  LANGUAGE sql;

By using an aggregate function, all underlying rows are processed in any case. The function returns bigint (that's what count() returns), so we get the number of rows as result.

Alternative solution 3

If you need to return void for some unknown reason, you can cast:

CREATE OR REPLACE FUNCTION t(i INTEGER)
  RETURNS VOID AS
$func$
   SELECT count(g(id))::text::void
   FROM generate_series(1, i) id;
$func$  LANGUAGE sql;

The cast to text is a stepping stone because the cast from bigint to void is not defined.

edited Jun 12, 2018 at 23:05

answered Jun 12, 2018 at 10:59

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

1 Comment

jbet Over a year ago

Thank you. I accepted the other answer, because it was earlier and both answers are very good.

Collectives™ on Stack Overflow

Multiple ALTER TABLE ADD COLUMN in one SQL function call

2 Answers 2

Comments

Alternative solution 1

Alternative solution 2

Alternative solution 3

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Alternative solution 1

Alternative solution 2

Alternative solution 3

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related