PostgreSQL

11 September 2024 10:36

-- Create the trigger function
CREATE OR REPLACE FUNCTION log_material_changes()
RETURNS TRIGGER AS $$
DECLARE
    v_action TEXT;
    v_old_value TEXT;
    v_new_value TEXT;
    v_field TEXT;
    v_user_name TEXT;
    v_mpn TEXT;
    v_spn TEXT;
    v_physicaltype TEXT;
BEGIN
    -- Determine the action type
    IF TG_OP = 'INSERT' THEN
        v_action := 'CREATE';
        v_old_value := NULL;
        v_new_value := ROW(NEW.*)::TEXT;  -- Capturing new values
    ELSIF TG_OP = 'UPDATE' THEN
        v_action := 'MODIFY';
        v_old_value := ROW(OLD.*)::TEXT;  -- Capturing old values
        v_new_value := ROW(NEW.*)::TEXT;  -- Capturing new values
    ELSIF TG_OP = 'DELETE' THEN
        v_action := 'DELETE';
        v_old_value := ROW(OLD.*)::TEXT;  -- Capturing old values
        v_new_value := NULL;
    END IF;
 
    -- Assume user information and other context is available through session variables or application context
    v_user_name := current_user;  -- Modify this line if user context is provided differently
    v_mpn := COALESCE(NEW.manufacturer_part_number, OLD.manufacturer_part_number);
    v_spn := COALESCE(NEW.supplier_part_number, OLD.supplier_part_number);
    v_physicaltype := COALESCE(NEW.physicaltype, OLD.physicaltype);
 
    -- Insert the log record into pm_change_logs
    INSERT INTO pm_change_logs (
        module, action, user_id, created_at, updated_at, is_deleted, item_id,
        old_value, new_value, field, date, user_name, approver, mpn, spn,
        physicaltype, comments
    )
    VALUES (
        'material_detail_stg', v_action, NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,
        FALSE, COALESCE(NEW.item_id, OLD.item_id), v_old_value, v_new_value,
        NULL, CURRENT_TIMESTAMP, v_user_name, NULL, v_mpn, v_spn,
        v_physicaltype, NULL
    );
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Create the trigger on material_detail_stg table
CREATE TRIGGER trg_material_changes
AFTER INSERT OR UPDATE OR DELETE ON material_detail_stg
FOR EACH ROW EXECUTE FUNCTION log_material_changes();

PostgreSQL

11 September 2024 09:00

Hi, need help from someone to check if a trigger function i have written is correct

PostgreSQL

10 September 2024 17:55

Any document for PostgreSQL upgrade on AWS RDS ?

PostgreSQL

10 September 2024 10:24

Hey everyone,

I’ve been working as a DBA/Data Engineer for the past 6 years, and I’m trying to figure out what area I should enrich my knowledge in during my free time. I work with PostgreSQL, MySQL, and MongoDB (both infrastructure and application-level). I mainly use NiFi and Python, and I work in AWS, GCP, and on-premises environments.

I have above-average knowledge of Linux, so I’m capable of handling many tasks that fall under IT/DevOps roles independently. I’ve mostly learned everything on my own, and now I’m looking for areas where I should deepen my expertise or learn something new.

I’m considering the following options and would appreciate your help:

Learn Oracle
Get AWS/GCP Certification as a Cloud Data Engineer
Continue specializing in PostgreSQL, MySQL, and MongoDB
I’d love your suggestions and advice 🙂

PostgreSQL

09 September 2024 16:58

PostgreSQL Person of the Week interview with: Philippe Noël

https://postgresql.life/post/philippe_noel/

PostgreSQL

09 September 2024 16:25

I'm looking for recorded video classes

PostgreSQL

09 September 2024 16:21

Hii anybody have postgreSQL DBA classes please share

PostgreSQL

09 September 2024 13:39

I am looking for postgres consultant/freelancer.

Who can help us to setup infrastructure and configuration for our saas application

PostgreSQL

09 September 2024 10:23

They all should be able to. You could check out Nagios, Icinga2, CheckMK, ...

PostgreSQL

09 September 2024 10:12

Ah ok, this explains stuff i experienced in the wild

PostgreSQL

09 September 2024 10:08

You can't pg_dump newer PG versions. So dump using 16 and just try to feed it into your 13 cluster. YMMV.

PostgreSQL

09 September 2024 09:02

-bash-4.2$ pg_restore -h 10.10.10.20 -U postgres -p 5432 -d dbtest /var/lib/pgsql/backup/dbtest2.sql
pg_restore: error: unsupported version (1.15) in file header
-bash-4.2$

PostgreSQL

09 September 2024 09:01

Might require some editing of the resulting sql

PostgreSQL

09 September 2024 08:56

Hi team ,
It's possible to restoration data that backup from postgres 16 and restore into postgres 13?

PostgreSQL

09 September 2024 08:28

Hi everyone l would like to change my path from postgreSQL DBA to aws I have a 2 years of experience in postgreSQL DBA. Is it good to learn AWS. If it is good then Suggest me the best you tube channel to learn
#AWS

PostgreSQL

11 September 2024 09:21

Paste there the trigger let's check

PostgreSQL

10 September 2024 22:21

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

PostgreSQL

10 September 2024 11:21

Can you describe your problem in more detail?

PostgreSQL

10 September 2024 09:57

Error in postgresql while import file please help

PostgreSQL

09 September 2024 16:25

I have azure devops ,mern stack ,power bi and dotnet net classes recordings

PostgreSQL

09 September 2024 16:22

https://www.postgresqltutorial.com/postgresql-tutorial/

PostgreSQL

09 September 2024 15:24

Mern Stack videos in 800

PostgreSQL

09 September 2024 11:56

I did this work long time ago, it's not simple. Some DDL commands can be changed between versions of postgresql.
For example I met an issue with sequence defenition when AS INTEGER/BIGINT was added between versions into DDL command generated by pg_dump.
But it's possible to apply newest dump to oldest version of postgresql in some cases.
Patience, manual fixing dump and dump can be applied.

PostgreSQL

09 September 2024 10:17

What monitor and alert tool are you using?
Does anyone have a tool that can monitor postgres + mysql?

PostgreSQL

09 September 2024 10:09

So, pg_dump only works with the same version of cluster as the binary?

PostgreSQL

09 September 2024 09:03

i got this error: pg_restore: error: unsupported version (1.15) in file header

PostgreSQL

09 September 2024 09:02

(perhaps use the older binaries to export the db?)

PostgreSQL

09 September 2024 09:01

Assuming you never used any of the newest functions of postgresql 16, you should he able to pg_dump and pg_restore in textual (SQL) form

PostgreSQL

09 September 2024 08:44

Is anyone from Russia?

PostgreSQL

08 September 2024 18:07

i forget to write the parameter -C