0

I've a script that I simply paste into an SQL query window in POSTGRES 9.1 and run.

eg -- Begin Scripts -- Part 1

    DO
    $$
    BEGIN
    CREATE SEQUENCE base_listing_id_seq
      INCREMENT 1
      MINVALUE 1
      MAXVALUE 9223372036854775807
      START 1
      CACHE 1;
    ALTER TABLE base_listing_id_seq
      OWNER TO postgres;
    EXCEPTION
        WHEN duplicate_table THEN RAISE NOTICE 'sequence base_listing_id_seq already exists';
    END
    $$ LANGUAGE plpgsql;

    -- Part 2

    CREATE TABLE IF NOT EXISTS aes_fba_details
    (
      id serial NOT NULL,
      product_id integer NOT NULL,
      shippingprice numeric DEFAULT 0,
      fbadatetime date NOT NULL,
      currency text,
      CONSTRAINT aes_fba_details_pkey PRIMARY KEY (id)
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE aes_fba_details
      OWNER TO postgres;

    -- End Scripts

What I want to do is to run part 1 or part 2 depending on a value (a version string if you like) that is read in from a table.

eg

    myVariable = SELECT version-string FROM versionTable;

    ... 
    DO PART 1
    ...

    If myVariable > 1 then
        ...
        DO PART 2
        ...
    End if

Is this even possible? I apologies in advance if I've mixed up the terminology.

1 Answer 1

1

It's possible, but you'll need to do all your DDL within a big DO block that uses IF conditions to decide what to do.

You cannot use variables or IF statements in plain SQL, only in PL/PgSQL.

Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, one big DO is fine with me. Can you help with an outline of what I'm trying to achieve?
@Sai-LeungLee I don't really know what you need. You've pretty much written the code you want. You know how to use PL/PgSQL DO blocks, as you have one in your example. So just wrap it in a DO block with appropriate IF ... THEN conditions. See the manual for PL/PgSQL control structures for how to use an IF statement.
Thanks for that but I'm a little unsure on how to initialise a variable with data returned from an SQL statement: myVariable = SELECT version-string FROM versionTable; Once I've got this bit working, I'm sure I'll be able to hack together the script. I know nothig about the DO bits of code (other than what I've been able to cobble together from Google searches). SQL and functions I'm fine with (to a point) but then I get confused as to how to tie them all together.
@Sai-LeungLee Please read the PL/PgSQL manual for details. Look at the DECLARE statement and the SELECT ... INTO ... statement for PL/PgSQL (this is different to normal SQL SELECT ... INTO ...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.