PostgreSQL: Creating a function which accepts multiple valuesReport
I want to write a function which will add insert record and then insert one or more records in a related table. I think I know what to do inside the function, but I don’t know what the function signature should look like.
Here is a mockup sample:
CREATE TABLE sales(id SERIAL, customer id, sold date); CREATE TABLE saleitems(SERIAL, sale int, details varchar, price numeric(6,2)); SELECT addSale(42, '2016-01-01', values ('stuff',13),('more stuff',42),('things',3.14),('etc',0)) items(price,details)); CREATE OR REPLACE FUNCTION addSale(customer,sold,items) RETURNS int AS $$ -- I think I can handle the rest $$ LANGUAGE sql;
The salient points:
- I would like to be able to use the
VALUES (…) name(…)construct as an argument — is this possible?
- The real problem, I think, is the last parameter
items. What is the appropriate type of this?
- I would like the language to be
SQL, since my next step is to translate this into other dialects (MySQL & SQL Server). However, I’ll do whatever is needed.
Eventually I will wrap the code body inside a transaction, and return the new
The question is: what is the correct parameter to accept a table expression in the
solved 06 months 1 Answer 39 views 0