Error bei Trigger

[Kirby.exe]

Also ich versuche gerade zwei Trigger in meine Docker Init Script einzubinden. Ich habe das Init Script bereits ohne den Part getestet und dort bekomme ich keine Fehler...Nur wenn ich es wieder einbaue.

CREATE FUNCTION creation_datetime() RETURNS trigger
                          LANGUAGE plpgsql
                          AS $$
                      BEGIN
                          NEW.creation_date = CURRENT_TIMESTAMP;
                          RETURN NEW;
                      END $$;
    CREATE FUNCTION sales_datetime() RETURNS trigger
                  LANGUAGE plpgsql
                  AS $$
              BEGIN
                  NEW.sales_datetime = CURRENT_TIMESTAMP;
                  RETURN NEW;
              END $$;
    CREATE TRIGGER customer_creation_date
                BEFORE INSERT ON customer
                FOR EACH ROW EXECUTE PROCEDURE creation_datetime();
    CREATE TRIGGER sales_sale_datetime
                BEFORE INSERT ON sales
                FOR EACH ROW EXECUTE PROCEDURE sales_datetime();

Die Fehlermeldung ist:

db_1       | 2021-08-10 16:53:17.525 UTC [200] ERROR:  syntax error at or near "1" at character 125
db_1       | 2021-08-10 16:53:17.525 UTC [200] STATEMENT:  CREATE FUNCTION creation_datetime() RETURNS trigger
db_1       |                               LANGUAGE plpgsql
db_1       |                               AS 1
db_1       |                           BEGIN
db_1       |                               NEW.creation_date = CURRENT_TIMESTAMP;
db_1       | ERROR:  syntax error at or near "1"
db_1       | LINE 3:                           AS 1
db_1       |                                      ^
db_1       |
db_1       | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1       |

 

[Mart]

CREATE TRIGGER customer_creation_date
BEFORE INSERT ON customer
FOR EACH ROW EXECUTE PROCEDURE creation_datetime();
CREATE TRIGGER sales_sale_datetime
BEFORE INSERT ON sales
FOR EACH ROW EXECUTE PROCEDURE sales_datetime();[/CODE]

trigger muss man zumindest in (einfachen) sql den Bereich immer eingrenzen mit / .... Trigger ... / das liegt nahe an dem was du hast an fehlern

 

[Kirby.exe]

trigger muss man zumindest in (einfachen) sql den Bereich immer eingrenzen mit / .... Trigger ... / das liegt nahe an dem was du hast an fehlern

Aber ich grenze den Trigger doch mit der Funktion ein oder habe ich das falsch verstanden?

 

[Robert Zenz]

Augenscheinlich wird "$$" ersetzt (naemlich mit "1") durch etwas bevor das Skript an die Datenbank uebergeben wird.

 

[Kirby.exe]

Augenscheinlich wird "$$" ersetzt (naemlich mit "1") durch etwas bevor das Skript an die Datenbank uebergeben wird.

Die große Frage ist why xD Ich schicke einfach mal das gesamte Init Ding:

#!/bin/bash
set -e

users=`cat /docker-entrypoint-initdb.d/users`
echo "Creating Shop Database"
for user in $users ;
do
    echo Creating user $user
    user_pw=`echo -n $user | md5sum | cut -c -5`

    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER $user;
    CREATE DATABASE $user;
    REVOKE connect ON DATABASE $user FROM PUBLIC;
    GRANT ALL PRIVILEGES ON DATABASE $user TO $user;
    ALTER USER $user WITH PASSWORD '$user_pw';
    GRANT pg_read_server_files TO $user;
EOSQL

    echo Creating user sample database
    echo $user_pw | psql -W --username $user --dbname $user -f /docker-entrypoint-initdb.d/sample/dvdrental.sql

done

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE shop;
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "shop" <<-EOSQL
    CREATE TABLE IF NOT EXISTS address (
                    address_id SERIAL PRIMARY KEY,
                    street VARCHAR NOT NULL,
                    house_nr VARCHAR NOT NULL,
                    postcode VARCHAR(5) NOT NULL,
                    district VARCHAR,
                    city VARCHAR NOT NULL,
                    country VARCHAR NOT NULL
                );
    CREATE TABLE IF NOT EXISTS customer (
                    customer_id SERIAL PRIMARY KEY,
                    first_name VARCHAR NOT NULL,
                    last_name VARCHAR NOT NULL,
                    email VARCHAR NOT NULL,
                    password VARCHAR NOT NULL,
                    user_group VARCHAR NOT NULL Check(user_group = 'Admin' or user_group = 'User'),
                    address_id INTEGER REFERENCES address(address_id),
                    creation_date TIMESTAMP,
                    UNIQUE(email)
                );
    CREATE TABLE IF NOT EXISTS manufacture (
                    manufacture_id SERIAL PRIMARY KEY,
                    manufacture VARCHAR NOT NULL
                );
    CREATE TABLE IF NOT EXISTS category (
                    category_id SERIAL PRIMARY KEY,
                    category VARCHAR NOT NULL
                );
    CREATE TABLE IF NOT EXISTS item (
                    item_id SERIAL PRIMARY KEY,
                    name VARCHAR NOT NULL,
                    price FLOAT NOT NULL,
                    manufacture_id INTEGER REFERENCES manufacture(manufacture_id),
                    category_id INTEGER REFERENCES category(category_id)
                );
    CREATE TABLE IF NOT EXISTS sales (
                    sales_datetime TIMESTAMP,
                    item_id INTEGER REFERENCES item(item_id),
                    customer_id INTEGER REFERENCES customer(customer_id)
                );
    CREATE INDEX customer_email_idx ON customer (email);
    CREATE SEQUENCE IF NOT EXISTS customer_customer_id_seq
                        START WITH 1
                        INCREMENT BY 1
                        NO MINVALUE
                        NO MAXVALUE
                        CACHE 1;
    CREATE FUNCTION creation_datetime() RETURNS trigger
                          LANGUAGE plpgsql
                          AS $$
                      BEGIN
                          NEW.creation_date = CURRENT_TIMESTAMP;
                          RETURN NEW;
                      END $$;
    CREATE FUNCTION sales_datetime() RETURNS trigger
                  LANGUAGE plpgsql
                  AS $$
              BEGIN
                  NEW.sales_datetime = CURRENT_TIMESTAMP;
                  RETURN NEW;
              END $$;
    CREATE TRIGGER customer_creation_date
                BEFORE INSERT ON customer
                FOR EACH ROW EXECUTE PROCEDURE creation_datetime();
    CREATE TRIGGER sales_sale_datetime
                BEFORE INSERT ON sales
                FOR EACH ROW EXECUTE PROCEDURE sales_datetime();
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "shop" <<-EOSQL
    CREATE USER lager_user;
    GRANT SELECT ON TABLE item TO lager_user;
    GRANT SELECT ON TABLE manufacture TO lager_user;
    GRANT SELECT ON TABLE category TO lager_user;
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "shop" <<-EOSQL
    CREATE USER marketing_user;
    GRANT SELECT (customer_id, first_name, last_name, email) ON TABLE customer TO marketing_user;
EOSQL

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "shop" <<-EOSQL
    CREATE USER statistik_user;
    GRANT SELECT ON TABLE item TO statistik_user;
    GRANT SELECT ON TABLE manufacture TO statistik_user;
    GRANT SELECT ON TABLE category TO statistik_user;
    GRANT SELECT (sales_datetime, item_id) ON TABLE sales TO statistik_user;
EOSQL

 

[Kirby.exe]

Mhh scheinbar erkennt er die $$ als Prozess ID und dann ist der Rest ja bzgl Syntax falsch 🙁 Aber mit Escaping geht es jetzt 🙂