CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
JamesMatson
JamesMatson

Reputation: 2932

Unable to create stored procedure in PostgreSQL: unterminated dollar-quoted string at or near "$$

I am losing my mind. In SQL Server, creating a stored procedure is very simple. There is a simple structure, you define it, and create. Done. In Postgres however, I have seen so many variations on the syntax for creating a function or stored procedure, and i have yet to be able to create one at all. I always get errors. Always. On any example I've tried online.

The example below fails with this error:

[Sep 3, 2020 12:26 PM] 42601: unterminated dollar-quoted string at or near "$$ DECLARE _schemaname text; _tablename text; _key text; _value text; _columns text[]; _values text[]; BEGIN SELECT pg_namespace.nspname, pg_class.relname INTO STRICT _schemaname, _tablename FROM pg_class JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace) WHERE pg_class.oid = $1; FOR _key IN SELECT columns.column_name FROM information_schema.columns WHERE columns.table_schema = _schemaname AND columns.table_name = _tablename ORDER BY columns.ordinal_position LOOP EXECUTE format($s$SELECT format('%%L', ((($1)::%s).%I))$s$, $1, _key) USING $2 INTO STRICT _value"

I don't know how to resolve this. I did some searching, and tried the suggestion of changing the $$ to $BODY$ at the top and bottom of the script, but it fails with the same error. What am I doing wrong?

Here is the stored procedure as defined on another website.

CREATE OR REPLACE FUNCTION create_insert_statement(regclass, anyelement) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
_schemaname text;
_tablename text;
_key text;
_value text;
_columns text[];
_values text[];
BEGIN
SELECT pg_namespace.nspname, pg_class.relname
INTO STRICT _schemaname, _tablename
FROM pg_class
JOIN pg_namespace
    ON (pg_namespace.oid = pg_class.relnamespace)
WHERE pg_class.oid = $1;

FOR _key IN
    SELECT columns.column_name
    FROM information_schema.columns
    WHERE columns.table_schema = _schemaname
      AND columns.table_name = _tablename
    ORDER BY columns.ordinal_position
LOOP
    EXECUTE format($s$SELECT format('%%L', ((($1)::%s).%I))$s$, $1, _key) USING $2 INTO STRICT _value;
    _columns := _columns || _key;
    _values := _values || _value;
END LOOP;
RETURN format('INSERT INTO %s (%s) VALUES (%s)', $1, array_to_string(_columns, ','), array_to_string(_values, ','));
END;
$$;

Upvotes: 0

Views: 667

Answers (2)

jjanes
jjanes

Reputation: 44373

You are using a broken client, like HeidiSQL, which mangles the text you enter before transmitting it to the database.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247830

You are using the wrong database client, because the function is fine.

Probably your client is not hip to dollar quoting and thinks that the SQL statement ends with the first semicolon in the function body.

You could try to avoid dollar quoting and double and quadruple the single quotes as appropriate, or you can use better client software.

Upvotes: 1

Related Questions