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?

Answer 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.

Answer 2

Basics about assigning variables in PL/pgSQL:

• Store query result in a variable using in PL/pgSQL

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) 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$  LANGUAGE plpgsql;

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.

Related:

• 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.