Question 46279 in Debian Question posted in
The oficial documentation can be found here.

I've got a Syntax Error in FOREACH near Parameter name in PostgreSQL – Debian

So, let me start with saying that I’m a complete Noob when it comes to SQL.

Ive Got two tables within a Schema: Attributes and Skills.

I wanted to automatically add the Skills for Each Attribute, since I already got all their Names in the "Modifies" column of the Attributes table, and well, also their Attribute.

The Code for The Function is the Following:

CREATE OR REPLACE FUNCTION AddSkFromList(p_Attr "Cyberpunk2020"."Attr", p_SkList text[]) RETURNS void AS $$
DECLARE
    v_SkName text;
BEGIN
    FOREACH v_SkName IN p_SkList LOOP
        INSERT INTO "Cyberpunk2020"."Skills" ("Attribute", "Name")
        VALUES (p_Attr, v_SkName);


    END LOOP;
END;
$$ LANGUAGE plpgsql;

Which gives me the Syntax Error:

ERROR:  syntax error at or near "p_SkList"
LINE 5: FOREACH v_SkName IN p_SkList LOOP
                            ^

I sadly am unable to figure out why this function doesn’t work, according to the Postgres Docs the FOREACH statement requires a target and an Expression, in all Examples I’ve found the target got Declared in the Declare Block [Duh], and the Expression can be any kind of Array. I’ve tried setting the p_SkList parameter to just text, but the error is the same. Do I fundamentally misunderstand something about Postgres and the array to loop over has to be predefined? I also set the search_path to the corrosponding Shema, in which I Want to create the Function, which is the same one that houses the Tables, and as you can see below, I’m able to access the tables with a simple d+ $shema_name

The Attributes Table looks Like the Following:

                              Table "Cyberpunk2020.Attributes"
  Column  |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
----------+--------+-----------+----------+---------+----------+--------------+-------------
 Attr     | "Attr" |           | not null |         | plain    |              |
 name     | text   |           |          |         | extended |              |
 Modifies | text[] |           |          |         | extended |              |
Indexes:
    "Attributes_pkey" PRIMARY KEY, btree ("Attr")
Child tables: public."Attributes"
Access method: heap

The Skills Table Like This:

d+ "Skills"
                                  Table "Cyberpunk2020.Skills"
    Column    |  Type  | Collation | Nullable | Default | Storage  | Stats target | Description
--------------+--------+-----------+----------+---------+----------+--------------+-------------
 Attribute    | "Attr" |           | not null |         | plain    |              |
 Name         | text   |           | not null |         | extended |              |
 Desciption   | text   |           |          |         | extended |              |
 IPMultiplier | bigint |           |          | 1       | plain    |              |
 Modifies     | text[] |           |          |         | extended |              |
Indexes:
    "Skills_pkey" PRIMARY KEY, btree ("Attribute", "Name")
Child tables: public."Skills"
Access method: heap

Postgres version

                                                          version
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.9 (Debian 13.9-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Thanks in Advance for all pointers in the right direction, as I am thoroughly confused.

I am expecting the Function to loop over the given text array p_SkList and insert the found names with the corrosponding Attribute as a new Row into "Skills"

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The syntax is IN ARRAY array_expr, not IN array_expr. – Laurenz Albe

    That's all that was wrong, thank you very much, i feel pretty blind now ^^

    This Code works:

    CREATE OR REPLACE FUNCTION AddSkFromList(p_Attr "Cyberpunk2020"."Attr", p_SkList text[]) RETURNS void AS $$
DECLARE
    v_SkName text;
BEGIN
    FOREACH v_SkName IN ARRAY p_SkList LOOP
        INSERT INTO "Cyberpunk2020"."Skills" ("Attribute", "Name")
        VALUES (p_Attr, v_SkName);


    END LOOP;
END;
$$ LANGUAGE plpgsql;

  2. 0

    Something like this should work, using UNNEST() to get all items from the array. This avoids the loop and you don’t need plpgsql either:

    CREATE OR REPLACE FUNCTION AddSkFromList(p_Attr "Cyberpunk2020"."Attr", p_SkList text[]) 
RETURNS void 
LANGUAGE sql;
AS 
$$
    INSERT INTO "Cyberpunk2020"."Skills" ("Attribute", "Name")
    SELECT  p_Attr
        ,   v_SkName
    FROM    UNNEST(p_SkList) AS sub(v_SkName);
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search