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');

On Fri, Apr 9, 2021 at 10:33 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Doc comments form <noreply(at)postgresql(dot)org> writes:
> > Example on this page is not working
> > https://www.postgresql.org/docs/13/pltcl-trigger.html
> > Gives ERROR: bind message supplies 1 parameters, but prepared statement
> ""
> > requires 0
>
> Works fine for me, when copied-and-pasted into psql.
>
> The error message you quote looks a bit like whatever client-side
> code you're using has decided that the $1 references in the function
> body are query parameters. They are not. The function body is a
> dollar-quoted string literal and should be sent unmodified.
>
> regards, tom lane
>