Setting variable in a Postgres function

Question

CREATE OR REPLACE FUNCTION "freeTicket" (eid integer NOT NULL)

DECLARE
    couponCode text 
BEGIN


INSERT INTO purchases p (cid, pdate, eid, ccode) 
VALUES
(
  SELECT p.cid, GETDATE(), $1, couponCode FROM purchase p
  GROUP BY p.cid
 HAVING COUNT(1) > 5
  ORDER BY p.cid
);

END; LANGUAGE plpgsql;

I need to set the variable of couponCode to the output of:

Select code from couponCode where eid = $1 and percentage = 100;

And use it in the insert query above.
What is the best way to do this?

Laurenz Albe · Accepted Answer · 2018-03-25 19:44:27Z

1

That would be SELECT <expressions> INTO <variables> FROM ..., but you can do it all in one statement:

INSERT INTO purchases p (cid, pdate, eid, ccode) 
   SELECT p.cid,
          current_date,
          $1,
          (SELECT code FROM couponcode
           WHERE eid = $1 AND percentage = 100)
   FROM purchase p
   GROUP BY p.cid
   HAVING COUNT(1) > 5:

ORDER BY makes no sense here.

answered Mar 25, 2018 at 19:44

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erwin Brandstetter · Accepted Answer · 2024-11-28 01:17:29Z

Basics about assigning variables in PL/pgSQL:

Store query result in a PL/pgSQL variable

Apart from that, your function has a number of syntax errors and other problems. Starting with:

CREATE OR REPLACE FUNCTION "freeTicket" (eid integer NOT NULL)

DECLARE ...

NOT NULL isn't valid syntax here.
You must declare the return type somehow. If the function does not return anything, add RETURNS void.
For your own good, avoid CaMeL-case identifiers in Postgres. Use legal, lower-case identifiers exclusively if possible. See:
Are PostgreSQL column names case-sensitive?

The function would work like this:

CREATE OR REPLACE FUNCTION free_ticket(_eid integer, OUT _row_ct int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   coupon_code text;  -- semicolon required
BEGIN
   INSERT INTO purchases (cid, pdate, eid, ccode)
   SELECT cid, now()::date, _eid
       , (SELECT code FROM couponCode WHERE eid = _eid AND percentage = 100)
   FROM   purchase
   GROUP  BY cid
   HAVING COUNT(*) > 5  -- count(*) is faster
   ORDER  BY cid;       -- ORDER BY is *not* pointless.

   GET DIAGNOSTICS _row_ct := ROW_COUNT;
END
$func$;

The added OUT row_ct int is returned at the end of the function automatically. It obviates the need for an explicit RETURNS declaration.

You also had a table alias in:

INSERT INTO purchases p (cid, pdate, eid, ccode)

But INSERT statements require the AS keyword for aliases to avoid ambiguity (unlike other DML statements). So: INSERT INTO purchases AS p .... But no need for an alias since there is no ambiguity in the statement.

Count the rows affected by plpgsql function

Asides: Two tables named purchase and purchases, that's bound to lead to confusion. And the second table might also be replaced with a VIEW or MATERIALIZED VIEW.

Collectives™ on Stack Overflow

Setting variable in a Postgres function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related