Convert some rows to column SQL , Query , Select, on PostgreSql

Dapat dilihat pada gambar dibawah ini. yaitu ketika ingin melakukan query suatu table dan menghasilkan result seperti pada gambar dibawah ini di postgre bisa menggunakan suatu alternatif cara yaitu menggunakan agregate.

Sebagai Contoh dibawah ini adalah fungsi untuk menggabungkan dua variable bertipe Text.

CREATE OR REPLACE FUNCTION xx_text_accum(text, text) 
RETURNS text 
LANGUAGE SQL 
AS $$
 SELECT $1||$2||', ';
  
$$;

$1 merupakan text pertama yang kemudian di gabungkan dengan $2 atau text kedua secara recursive. kemudian tanda ', ' (coma) berfungsi sebagai delimiter.


Fungsi kedua xx_text_accum_last(text) yaitu fungsi yang digunakan untuk menghilangkan karakter coma pada text terakhir.

CREATE OR REPLACE FUNCTION xx_text_accum_last(text) 
RETURNS text 
LANGUAGE SQL 
AS $$
  select substr(trim($1), 0, length($1) - 1);
$$;

Dibawah ini merupakan cara untuk membuat Agregate selain dari pada yang sudah exsisting di postgre seperti sum() average() count() dll.

CREATE AGGREGATE text_accum (text)
(
sfunc = xx_text_accum,
stype = text,
initcond = '',
FINALFUNC = xx_text_accum_last
);

Dan untuk melihat hasilnya bisa menggunakan perintah (query) seperti di bawah ini 

Select col1, text_accum(col2) from table_name group by col1;

Sekian langkah dari proses convert rows to column pada query database. terimakasih :)

Categories: Databases, Tips and Trick

Contoh Trigger Procedure Postgres, PostgreSQL

Some syntax can be use in trigger is :

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETEoperations.

---

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

---

TG_NAME

Data type name; variable that contains the name of the trigger actually fired.

---

TG_WHEN

• Data type text; a string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger's definition.

---

TG_LEVEL

Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

---

TG_OP

Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

---

TG_RELID

Data type oid; the object ID of the table that caused the trigger invocation.

---

TG_RELNAME

Data type name; the name of the table that caused the trigger invocation. This is now deprecated, and could disappear in a future release. Use TG_TABLE_NAME instead.

---

TG_TABLE_NAME

Data type name; the name of the table that caused the trigger invocation.

---

TG_TABLE_SCHEMA

Data type name; the name of the schema of the table that caused the trigger invocation.

---

TG_NARGS

Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

---

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value.

---

Example of function (Store Procedure) to will be execute by trigger :

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

And then create a trigger to execute function above:

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

Hope this example can be help you. for detail information you can visit to postgres doc

Categories: Databases