Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite

I am new to PostgreSQL. Could anybody please correct this query.

BEGIN TRANSACTION;

BEGIN;
    CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );


    COMMIT TRANSACTION;
    RAISE NOTICE 'Task completed sucessfully.'
EXCEPTION;
    ROLLBACK TRANSACTION;
    RAISE ERROR @ErrorMessage, @LineNumber, @ErrorState --how to catch errors and display them????
END;

Questions:

  1. How to print a message like 'PRINT' in T-SQL?
  2. How to raise errors with exception information?
share|improve this question
what language are you writing in? you seem to be mixing the C macros with direct SQL statements, and you say "plpgsql" in the tags. – araqnid Feb 2 '11 at 17:32
1  
I am new to postgres. Can you guide me achieve this inside postgres query. – Nick Binnet Feb 2 '11 at 17:34
1  
Thanks. Edited. I have tried a couple of other syntaxes but with vain. – Nick Binnet Feb 2 '11 at 17:45

3 Answers

up vote 8 down vote accepted

Use the DO statement, a new option in version 9.0:

DO LANGUAGE plpgsql
$$
BEGIN
CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );

    RAISE NOTICE 'Task completed sucessfully.';    
END;
$$;
share|improve this answer
up vote 6 down vote

To catch the error message and its code:

do $$


begin


    create table yyy(a int);
    create table yyy(a int); -- this will cause an error



exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                 'Transaction was rolled back';

    raise notice '% %', SQLERRM, SQLSTATE;
end;


$$ language 'plpgsql';

Haven't found the line number yet

share|improve this answer
up vote 4 down vote

You could write this as a psql script, e.g.,

START TRANSACTION;
CREATE TABLE ...
CREATE TABLE ...
COMMIT;
\echo 'Task completed sucessfully.'

and run with

psql -f somefile.sql

Raising errors with parameters isn't possible in PostgreSQL directly. When porting such code, some people encode the necessary information in the error string and parse it out if necessary.

It all works a bit differently, so be prepared to relearn/rethink/rewrite a lot of things.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.