PostgreSQL Insert Returning ID (Primary Key)

[RichAlgeni]

I was researching a way to get the Primary Key of an inserted row back easily from Postgres. It can be done using the RETURNING clause, but you need to use the DatabaseQuery command, not DatabaseUpdate.

Code: Select all

CREATE TABLE test_table (
    test_table_key            SMALLSERIAL PRIMARY KEY,
    test_table_id             SMALLINT    UNIQUE NOT NULL,
    test_table_name           VARCHAR NOT NULL,
    test_table_description    VARCHAR NOT NULL,
    last_update               TIMESTAMP  NOT NULL DEFAULT NOW()
);

Code: Select all

EnableExplicit

OpenConsole()

UsePostgreSQLDatabase()

Define result.i
Define dbString.s
Define debugLine.s
Define databaseNumber.i

databaseNumber = OpenDatabase(#PB_Any, "host=localhost port=5432 dbname=postgres", "postgres", "")
If databaseNumber
    PrintN("Opened PostgreSql datbase to " + Str(databaseNumber))
    dbString = "insert into test_table (test_table_id, test_table_name, test_table_description) "
    dbString + "VALUES(1, 'TEST NAME', 'TEST DESCRIPTION') RETURNING test_table_key;"
    result   = DatabaseQuery(databaseNumber, dbString)

    If result
        PrintN("Update completed ok")
        While NextDatabaseRow(databaseNumber)
            debugLine = DatabaseColumnName(databaseNumber, 0) + " = "
            debugLine + GetDatabaseString(databaseNumber, 0)
            PrintN(debugLine)
        Wend
    Else
        PrintN("Update did not run!!!")
    EndIf

    CloseDatabase(databaseNumber)
Else
    PrintN("cannot open PureBasic, " + DatabaseError())
EndIf

Input()

CloseConsole()

End

Code: Select all

Opened PostgreSql datbase to 3150832
Update completed ok
test_table_key = 1

Notes: I always use a serial primary key, unless the table will contain only a small number of rows. In my experiments, only one row is returned, and only one value. It doesn't matter what you name it in the returning clause, or if you include multiple fields. Only the primary key as the value will be returned.

Also, I always create a last_update column, and auto update it with a trigger. Here is the trigger code:

Code: Select all

CREATE OR REPLACE FUNCTION update_row_last_update_function_()
RETURNS TRIGGER
AS
$$
BEGIN
    -- ASSUMES the table has a column named exactly "last_update".
    -- Fetch date-time of actual current moment from clock, rather than start of statement or start of transaction.
    NEW.last_update = now();
    RETURN NEW;
END;
$$
language 'plpgsql';

The above code is only needed to be entered once. Below is needed for each table.

Code: Select all

CREATE TRIGGER trigger_on_test_table
BEFORE UPDATE
ON test_table
FOR EACH ROW
EXECUTE PROCEDURE update_row_last_update_function_();