create temporary table into bagin and end scope

yehudit

Hi,
I'm trying to create a temporary table within the scope of begin and end:

DO $$
DECLARE
Is_Full_Exect INT;
BEGIN
Is_Full_Exect: = 5; - since y refers to x, x = 5
CREATE TEMP TABLE IF NOT EXISTS StringMapBase (VALUE VARCHAR (300) NULL, AttributeValue INT NULL, AttributeName VARCHAR (80) NOT NULL);
END;
$$;

And accepts the following error:

ERROR 10448: PL / vSQL parser failed at 6.12-15 of source string: syntax error, unexpected IDENT, expecting: = or <- at character 109 LINE 6: CREATE TEMP TABLE IF NOT EXISTS StringMapBase (VALUE VAR ...

I would be very grateful to anyone who can help me understand the error

marcothesane

Trying it myself:

DO $$
  DECLARE
    Is_Full_Exect INT;
  BEGIN
    Is_Full_Exect: = 5; - since y refers to x, x = 5
    PERFORM CREATE TEMP TABLE IF NOT EXISTS StringMapBase (
        VALUE VARCHAR (300) NULL
     , AttributeValue INT NULL
    , AttributeName VARCHAR (80) NOT NULL
    ) ON COMMIT PRESERVE ROWS KSAFE 0;
   END;
$$;

-- out ERROR 10448: PL/vSQL parser failed at 5.18 of source string: syntax error, unexpected UNKNOWN,
-- out expecting := or <- at character 65
-- out LINE 5: Is_Full_Exect: = 5; - since y refers to x, x = 5
-- out ^
I have problems with this site, as it does not show code in a fixed font. So I forced it with a trick. Note that the caret (^) is just under the colon : that is separated by a blank from the equal sign =. Try removing that blank/space ....

And: you will have to add the PERFORM keyword to execute a SQL command from within an anonymous block or a SP:
Try this:

DO $$
  DECLARE
    Is_Full_Exect INT;
  BEGIN
    Is_Full_Exect:= 5;
    PERFORM
      CREATE LOCAL TEMP TABLE IF NOT EXISTS StringMapBase (
        VALUE VARCHAR (300) NULL
      , AttributeValue INT NULL
      , AttributeName VARCHAR (80) NOT NULL
      ) ON COMMIT PRESERVE ROWS KSAFE 0;
  END;
$$;
-- out DO
-- out Time: First fetch (0 rows): 21.931 ms. All rows formatted: 22.128 ms                                                                                                                               

marcothesane

It showed correct in Preview, but when I posted it, all blanks before the caret were removed. I you run it from vsql in a Linux command line, the caret shows up where I told you ... Sorry for the mess, I can't change it ...

yehudit

It helped!!!
Thank you very much!!
I'm really new to vsql so not yet familiar with the syntax