How to access parameters inside a Netezza stored procedure?

Question

This is my stored procedure:

nzsql -u user -pw pass -c "CREATE OR REPLACE PROCEDURE INSERT_LOGIC(varchar(50),varchar(20),varchar(40)) RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC
DECLARE
t1 ALIAS FOR $1;
t2 ALIAS FOR $2;
t3 ALIAS FOR $3;
BEGIN

INSERT INTO ABC..XYZ
(select '$t1','$t2','$t3' from ABC..PQR limit 10);

END;
END_PROC;"

The ALIAS FOR is the only way I found on the internet to do this but I get the following error:

NOTICE:  plpgsql: ERROR during compile of INSERT_LOGIC near line 3
ERROR:  syntax error, unexpected ERROR, expecting VARIABLE or WORD at or near "t1Stuff"

How do I access the three "varchar variables" that I pass to the stored procedure inside the same?

score 1 · Answer 1 · answered May 04 '16 at 05:58

Here is an example similar to your requirement and its working. I am using two tables 'tab1' and 'tab2' with following description:



    $ nzsql -d test -c "\d tab1"
                         Table "TAB1"
     Attribute |     Type      | Modifier | Default Value
    -----------+---------------+----------+---------------
     COL1      | INTEGER       |          |
     COL2      | CHARACTER(10) |          |
     COL3      | INTEGER       |          |
    Distributed on hash: "COL1"

    $ nzsql -d test -c "\d tab2"
                         Table "TAB2"
     Attribute |     Type      | Modifier | Default Value
    -----------+---------------+----------+---------------
     C1        | INTEGER       |          |
     C2        | CHARACTER(10) |          |
     C3        | INTEGER       |          |
    Distributed on hash: "C1"

Following is the stored procedure code that I used:



    CREATE OR REPLACE PROCEDURE INSERT_LOGIC(varchar(50),varchar(20),varchar(40))
       RETURNS BOOL
       LANGUAGE NZPLSQL
       AS
    BEGIN_PROC
      DECLARE
        num_args int4;
        sql char(100);
        t1 ALIAS FOR $1;
        t2 ALIAS FOR $2;
        t3 ALIAS FOR $3;
      BEGIN
        num_args := PROC_ARGUMENT_TYPES.count;
        RAISE NOTICE 'Number of arguments: %', num_args;
        sql := 'INSERT INTO tab2 SELECT ' || t1 || ',' || t2 || ',' || t3 || ' FROM tab1 LIMIT 10 ';
        RAISE NOTICE 'SQL Statement: %', sql;
        EXECUTE IMMEDIATE sql;
      END;
    END_PROC;

Hope this will help!

Jeremy Fortune · Answer 2 · 2016-04-30T12:32:48.383

0

You're attempting to reference variables by putting a $ in front of the name, which is not valid.

Look at the example in the docs.

DECLARE
  logtxt ALIAS FOR $1;
  curtime timestamp;
BEGIN
  curtime := 'now()';
  INSERT INTO logtable VALUES (logtxt, curtime);
  RETURN curtime;
END

You should try

INSERT INTO ABC..XYZ
(select t1, t2, t3 from ABC..PQR limit 10);

Though it's possible that the column values won't resolve when used this way. If not, build a dynamic statement and execute it instead.

declare sql varchar;
sql := 'insert into abc..xyz select ' || t1 || ',' || t2 || ',' || t3 || ' from abc..pqr limit 10;'
execute immediate sql;

If you're passing values, not column names, as parameters:

declare sql varchar;
sql := 'insert into abc..xyz select ''' || t1 || ''',''' || t2 || ''',''' || t3 || ''' from abc..pqr limit 10;'
execute immediate sql;

edited Apr 30 '16 at 12:32

answered Apr 29 '16 at 20:10

Jeremy Fortune

2,459
1
18
21

Still not working. The problem is I have to use the select statement to insert. I know it works if do.. INSERT INTO ABC..XYZ VALUES (t1,t2,t3). However, I actually need to use the select statement cos my actual insert is something like this, INSERT INTO ABC..XYZ (select t1, t2, t3, p1, p2, p3 from ABC..PQR limit 10); where t1, t2, t3 come from the parameters passed and p1, p2 and p3 come from the table PQR. – Macopare Apr 29 '16 at 20:39
Are you passing strings to be inserted or column names to the procedure? – Jeremy Fortune Apr 30 '16 at 01:05
1

Are t1, t2, and t3 all meant to insert literal values while p1, p2, and p3 are all meant to insert whatever is in the columns of PQR? Jeremy's dynamic SQL solution should work for you, although you may want to use quote_ident or quote_literal depending on your intentions. – ScottMcG Apr 30 '16 at 17:27

How to access parameters inside a Netezza stored procedure?

2 Answers2