Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

create temporary table into bagin and end scope

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

Best Answers

  • marcothesanemarcothesane Administrator
    Answer ✓

    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                                                                                                                               
    
  • marcothesanemarcothesane Administrator
    Answer ✓

    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 ...

Answers

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.