CODEWITHSUNDEEP
postgresqlplpgsql
Sergio Flores
Sergio Flores

Reputation: 5427

How to return multiple INSERTED ID's in Postgresql?

I have a function which takes two arguments, the first one is an integer and the second one is an array of varchars.

I want to insert just the hashes that aren't previously inserted for the campaign and then return the inserted ids — in this case, the url_hash field of the campaigns_urls table — but I keep getting the following error:

ERROR:  column "hash" does not exist LINE 10:  RETURNING "hash"
                    ^ 
HINT:  There is a column named "hash" in table "*SELECT*", but it cannot be referenced from this part of the query.

I am calling a function like this:

-- SELECT * FROM assign_urls_to_campaign(1,'{Xelgb20Lw}')

CREATE OR REPLACE FUNCTION public.assign_urls_to_campaign(
    param_campaign_id integer,
    param_hashes character varying(20)[]
)
    RETURNS character varying(20)
    LANGUAGE 'plpgsql'
    VOLATILE
AS $BODY$
BEGIN

    INSERT INTO campaigns_urls ("campaign_id", "url_hash") 
    SELECT
        param_campaign_id as "id", "P"."hash"
    FROM "urls" AS "U"
    RIGHT OUTER JOIN (
        SELECT hash FROM UNNEST(param_hashes) AS "hash"
    ) AS "P"
    ON "U"."hash" = "P"."hash"
    WHERE "U"."hash" ISNULL
    RETURNING "hash";

END;
$BODY$;

Upvotes: 0

Views: 2396

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45930

There are more issues:

  1. If functions returns more than one row, then should to use SETOF keywords after RETURNS.

  2. PlpgSQL functions requires RETURN statement - in this case RETURN QUERY.

    create table test(a int);

create or replace function foo(int)
returns setof int as $$
begin
  return query
    insert into test 
      select v from generate_series(1,$1) g(v)
      returning a;
end;
$$ language plpgsql;

postgres=# select * from foo(3);
┌─────┐
│ foo │
╞═════╡
│   1 │
│   2 │
│   3 │
└─────┘
(3 rows)

Upvotes: 1

Related Questions