Search code examples
postgresql-9.3

ERROR: syntax error at or near "site" in PostgreSQL

I am creating a stored procedure which will accept array or record as argument.

  CREATE TYPE site AS(
  siteid  integer,
  packageid integer,
  status  text 
);

 CREATE OR REPLACE FUNCTION packageinfo(IN
info  site[]
)RETURNS VARCHAR  AS $$
DECLARE
    info_element  site;
BEGIN
    FOREACH info_element IN ARRAY info
    LOOP
        INSERT INTO sitepackage(
            siteid,
            packageid,
            status
        ) VALUES(
            info_element.siteid,
            info_element.packageid,
            info_element.status
        );
    END LOOP;
    RETURN 'OK';
END;
$$ LANGUAGE sql;

But it is giving an ERROR: syntax error at or near "site" LINE 11: info_element site;

Solution

  • With statement language SQL you are defining your function as an SQL function. The DECLARE block is part of the structure of PL/pgSQL functions so you have to create the function with LANGUAGE plpgsql.

    More info from the documentation: