I must be blind... - Mailing list pgsql-general

'Afternoon folks,

I think I'm going blind I just can't spot what I've done wrong. Can someone
have a quick glance at this function, and relevent table definitions, and tell
me what I've got wrong please?

The error message I'm getting when I try to use it with:

SELECT new_transaction_fn(9, 444, 4, 'B', now(), 'C');

is:

NOTICE:  Error occurred while executing PL/pgSQL function new_transaction_fn
NOTICE:  line 11 at assignment
ERROR:  parser: parse error at or near "SELECT"

(The select works and returns one row as I expect it to btw)


--
-- Tables
--

CREATE TABLE orders (
    id        INTEGER    NOT NULL DEFAULT nextval('order_seq') PRIMARY KEY,
    type        INTEGER REFERENCES order_type(id),
    instrument    INTEGER REFERENCES instrument(id),
    time        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    market_price    FLOAT8,
    price        FLOAT8,
    quantity    INTEGER,
    direction    CHAR(1) CHECK(direction = 'B' OR direction = 'S')
) WITHOUT OIDS;


CREATE TABLE transaction (
    id        INTEGER NOT NULL DEFAULT nextval('transaction_seq') PRIMARY KEY,
    order_id    INTEGER REFERENCES orders(id),
    time        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    price        FLOAT8,
    quantity    INTEGER,
    status        CHAR(1) CHECK(status = 'c' OR status = 'C')
) WITHOUT OIDS;


--
-- Function
--

CREATE OR REPLACE FUNCTION new_transaction_fn (
        integer,float8,integer,char(1),timestamp,char(1)
    ) RETURNS boolean AS '
    DECLARE
        ordid ALIAS FOR    $1;
        price ALIAS FOR    $2;
        quantity ALIAS FOR $3;
        dirn ALIAS FOR     $4;
        time ALIAS FOR     $5;
        status ALIAS FOR   $6;
    BEGIN
        -- check against order
        PERFORM
            SELECT 1
                FROM orders
                WHERE
                    id = ordid
                    AND
                    direction = dirn;
        IF NOT FOUND THEN
            RAISE EXCEPTION ''No order matching % / % found'', ordid, dirn;
        END IF;

        INSERT INTO transaction VALUES (
            nextval(''transaction_seq''),
            ordid,
            COALESCE(time, now()),
            price,
            quantity,
            COALESCE(status, ''C'')
        );

        RETURN TRUE;
    END;
    ' LANGUAGE 'plpgsql';

--
--
--


Thanks,

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants