Postgres Node fails multiple insert statements with parameterized inputs

[aMacDawg]

Environment
Node-RED version: 2.2.0
node-red-contrib-postgresql version: 0.8.0

Expected outcome: as a Node-Red editor user, I can propagate the msg.params variable to the postgres node in my Node-RED flow which will successfully execute a multiple insert statements using the numeric parameters approach.

Current outcome: the postgres node returns an error when attempting to perform multiple insert (and update!) statements in a single node query editor.

Error statement

error: {
message: "error: cannot insert multiple commands into a prepared statement"
}

Sample statement encountering error

INSERT INTO public.users ( "firstName","lastName","email","username","password","isPoliticallyExposed",
"onboardingStatus","apiKey","resetPasswordToken","resetPasswordTokenCreated","emailConfirmationToken",
"emailConfirmationDate","secondLastName","city","country","addressLine1","addressLine2","region",
"postalCode","dialingCode","phoneNumber","currentOrganizationId","cifId","politicalPosition","mfaToken",
"mfaTokenExpiry"
)VALUES (
    $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26
);
-- insert user record

-- 2. insert organization record
INSERT INTO public.organizations( "type","onboardingStatus","legalName","commercialName",
"natureofBusiness","businessType","totalShares","country","addressLine1","addressLine2","city",
"region","postalCode","taxId","createdBy","kybStatus",
)VALUES(
$29,$30,$31,$32,$33,$34,$35,$36,$37,$38,$39,$40,$41,$42,(select id from public.users where "username"=$4),$44
);

-- insert user_organization record
INSERT INTO public.user_organizations (
    "userId","role","organizationId"
)VALUES(
(select id from public.users where "username"=$1),'ADMIN',(select id from public.organizations where "username"=$4)
)
RETURNING id as user_organization_id;

UPDATE public.users SET "currentOrganizationId" = (select id from public.organizations where "legalName"=$4) WHERE username=$4;

Please consider, the postgres node behaves as expected when performing multiple inserts for non-numeric parameterized queries such as this:

CREATE TEMP TABLE organization(
    organization_id serial,
    organization_name varchar(128),
    organization_type varchar(64),
    created_date_time timestamp DEFAULT now()
);
INSERT INTO organization(organization_name,organization_type)VALUES('someone's org 1','personal'),('someone's org 2','personal'),
('someone's org3 org','personal'),('someone's org4','personal'),('someone's org5','personal'),('someone's org6 org','personal');
-- create user junction table
CREATE TEMP TABLE user__organization(user_id varchar(64), organization_id varchar(64),user_role varchar(32));
-- insert junction records into this temp table
INSERT INTO user__organization VALUES('1','1','Admin'),('3','2','Admin'),('4','3','Admin'),('2','5','Admin'),('5','6','Admin');

select u."id","firstName","lastName","email",user_role,o.organization_id,o.organization_name,o.created_date_time
FROM public.user u JOIN user__organization uo ON text(u."id") = uo.user_id
JOIN organization o ON uo.organization_id = text(o.organization_id);

[Alkarex]

Hello and sorry for the delay. I do not believe PostgreSQL supports this syntax. However, it should work with a little twist, grouping your queries in a WITH ... AS statement: https://www.postgresql.org/docs/current/queries-with.html

Alternatively, you could consider our (less safe) Mustache-based query template syntax https://github.com/alexandrainst/node-red-contrib-postgresql#sql-query-template