PostgreSQL: Creating a function which accepts multiple values

Report
Question

Please briefly explain why you feel this question should be reported .

Report Cancel

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:

  1. I would like to be able to use the VALUES (…) name(…) construct as an argument — is this possible?
  2. The real problem, I think, is the last parameter items. What is the appropriate type of this?
  3. 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 sales.id value.

The question is: what is the correct parameter to accept a table expression in the VALUES form?

solved 0
1 Answer 29 views 0

Answer ( 1 )

    0
    January 12, 2017 at 1:00 am

    Please briefly explain why you feel this answer should be reported .

    Report Cancel

    Your best bet here is to create a new type that holds the details and price of a product:

    CREATE TYPE product_details AS (
      details varchar,
      price   numeric(6,2)
    );
    

    Then you can define a function parameter of type product_details[], i.e. an array of product details. Since you want to have a SQL function and need to retrieve the value of the serial column of one insert for use in another insert, you need a CTE:

    CREATE FUNCTION addSale(_customer int, _sold int, _items product_details[]) RETURNS int AS
    $$
      WITH s AS (
        INSERT INTO sales (customer, sold) VALUES (_customer, _sold) RETURNING id;
      )
      INSERT INTO saleitems (sale, details, price)
        SELECT s.id, i.d, i.p
        FROM s, unnest(_items) i(d, p);
    $$ LANGUAGE sql;
    

    And then you call the function like so:

    SELECT addSale(42, '2016-01-01'::date,
                   ARRAY[('stuff',13),('more stuff',42),('things',3.14),('etc',0)]);
    
    Best answer

Leave an answer

Browse

What is the capital of Egypt ? ( Cairo )

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>