Re: PL/Tcl - examples not working

Ok, you are right works fine copying directly to psql. I was trying to do it from ValentinaStudio. But I still can't understand why my FUNCTION doesn't work.

I have a table "counter" where i keep track of gapless counters for different tables, my FUNCTION simply updates the "counter" table and adds value to NEW array, exactly like in the example. But doesnt work like that, so I need to put $1 in curly braces, like that ${1} when setting a new value on the NEW array.

Here is my code, if you cant help me figure out what I'm doing wrong I really appreciate :

-- CREATE TABLE "counter" --------------------------------------
CREATE TABLE "counter" (
"tbl" Text NOT NULL,
"num" Bigint NOT NULL,
CONSTRAINT "unique_counter_tbl" UNIQUE( "tbl" ) );
 ;
-- -------------------------------------------------------------

INSERT INTO "counter" ( "tbl", "num") VALUES ( 'invoice', 0 );
INSERT INTO "counter" ( "tbl", "num") VALUES ( 'proforma', 0 );
INSERT INTO "counter" ( "tbl", "num") VALUES ( 'payment', 0 );

CREATE OR REPLACE FUNCTION get_num () RETURNS trigger AS $$
    spi_exec "UPDATE counter SET num = num + 1 WHERE tbl='$TG_table_name' RETURNING num";
set NEW($1) $num
    return [array get NEW]
$$ LANGUAGE pltcl;

CREATE TRIGGER get_inv_number before insert ON invoice FOR EACH ROW EXECUTE PROCEDURE get_num('inv_num');
CREATE TRIGGER get_pro_number before insert ON proforma FOR EACH ROW EXECUTE PROCEDURE get_num('pro_num');
CREATE TRIGGER get_pay_number before insert ON payment FOR EACH ROW EXECUTE PROCEDURE get_num('pay_num');