CODEWITHSUNDEEP
sqlpostgresqlsyntaxsql-insertauto-increment
Joshua Schlichting
Joshua Schlichting

Reputation: 3460

pg_get_serial_sequence not recognizing table name

I'm attempting to get the last SERIAL ID created in a table by usingpg_get_serial_sequence() in a procedure, but I keep getting an error:

[42P01]ERROR: relation "user" does not exist

Where: SQL function "insert_new_user" statement 2

The User table certainly exists, as I can SELECT from it.

Below is the DDL script for creating the 2 tables being effected by my function:

CREATE TABLE users.User
(
    ID SERIAL PRIMARY KEY,
    Name TEXT NOT NULL UNIQUE,
    EmailAddress TEXT,
    ROWCREATE_TS TIMESTAMP,
    IS_ACTIVE BOOLEAN,
    INACTIVE_TS TIMESTAMP
);

CREATE TABLE users.Credential
(
    CredentialValue TEXT,
    UserId INT REFERENCES users.User(ID)
);

And here is the procedure that is returning the error:

/**
  Creates a new user
 */
CREATE OR REPLACE PROCEDURE users.INSERT_NEW_USER(userName TEXT, emailAddress TEXT, password TEXT)
LANGUAGE SQL
AS $$
    INSERT INTO users.User
    (
        NAME,
        EmailAddress,
        ROWCREATE_TS,
        IS_ACTIVE
    )
    SELECT
        INSERT_NEW_USER.userName,
        INSERT_NEW_USER.emailAddress,
        CURRENT_TIMESTAMP,
        TRUE
    WHERE
        NOT EXISTS(
            SELECT TRUE
            FROM users.User
            WHERE
                NAME = INSERT_NEW_USER.userName
        );

    INSERT INTO users.Credential
    (
        CREDENTIALVALUE,
        USERID
    )
    VALUES
    (
        INSERT_NEW_USER.password,
        (SELECT currval(pg_get_serial_sequence('user', 'id')))
    );
$$;

(NOTE: Before someone mentions it - I do not plan on storing passwords here in plain text.)

What can I do differently to either address this issue of pg_get_serial_sequence not recognizing the User table, or to ultimately get that last created ID stored into a usable variable that I can pass into my INSERT INTO users.Credential statement?

Upvotes: 1

Views: 766

Answers (1)

Joshua Schlichting
Joshua Schlichting

Reputation: 3460

This was solved by using a combination of suggestions in the comments.

I had renamed the table, as User is a PostgreSQL keyword, and also used the fully qualified name users.NewTableName within the pg_get_serial_sequence() call, as shown below.

SELECT currval(pg_get_serial_sequence('users.NewTableName', 'id')).

EDIT: It could be worth pointing out, for future readers with a similar issue, that the following also worked as a fix to my goal of getting that ID down to the 2nd statement. I was able to do it using WITH:

CREATE OR REPLACE PROCEDURE users.INSERT_NEW_USER(userName TEXT, emailAddress TEXT, password TEXT)
LANGUAGE SQL
AS $$
    WITH Inserted as(
        INSERT INTO users.Users
        (
            NAME,
            EmailAddress,
            ROWCREATE_TS,
            IS_ACTIVE
        )
        SELECT
            INSERT_NEW_USER.userName,
            INSERT_NEW_USER.emailAddress,
            CURRENT_TIMESTAMP,
            TRUE
        WHERE
            NOT EXISTS(
                SELECT TRUE
                FROM users.Users
                WHERE
                    NAME = INSERT_NEW_USER.userName
            )
        RETURNING ID
    )

    INSERT INTO users.Credential
    (
        CREDENTIALVALUE,
        USERID
    )
    VALUES
    (
        INSERT_NEW_USER.password,
        (SELECT Inserted.ID FROM Inserted)
    );
$$;

With that said, I would also like to advise readers that they should avoid using PostgreSQL keywords for naming tables, variables, etc.

Upvotes: 2

Related Questions