Netezza Variables In Identifiers

Question

Im writing a Netezza procedure and trying to write an insert with a variable in FROM clause. But Netezza seems to not allow this as I get a ERROR :

'INSERT INTO pkcount SELECT count(*) sk FROM $1 ' error ^ found "$1" (at char 53) expecting an identifier found a keyword

CREATE OR REPLACE PROCEDURE check_tbl (varchar(255))
RETURNS integer
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE 
tbl_name  ALIAS FOR $1;
BEGIN

CREATE TEMP TABLE pkcount (sk bigint);

INSERT INTO pkcount
SELECT count(*) sk FROM tbl_name;

END;
END_PROC;

Answer 1

You need to create dynamic sql in order to include the variable as a part of query

Below proc should work

CREATE OR REPLACE PROCEDURE check_tbl (varchar(255))
RETURNS integer
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE 
tbl_name  ALIAS FOR $1;
sql char(1000);
BEGIN

CREATE TEMP TABLE pkcount (sk bigint);

sql := 'INSERT INTO pkcount SELECT count(*) sk FROM '||tbl_name;
execute immediate sql;
RAISE NOTICE 'SQL Statement: %', sql;

END;
END_PROC;

Just in case you want to check the sql statement so RAISE NOTICE is added .