[SQL] Field or record level encryption / decryption
Hi, Anyone know what function i can use to encrypt / decrypt field or record ? When record saved, it saved in encrypt format. When i need to read data, i just call decrypt function. I am using MS Access 2003 and Postgresql 8.3 Thanks a lot. Regards, Hengky -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Field or record level encryption / decryption
Thanks to all who response my question. I have checked the doc, but it seems too advance for my postgresql knowledge. Other question is where can i get pgcrypto modules ? For simple record encryption that contains date field, varchar, integer and text, what encryption i can use ? As far as i know, there is no way to return MD5 result back to its original value. Is this true or not ? Thanks a lot On Aug 14, 2009, at 11:21 PM, Christopher Browne wrote: [email protected] (Hengky Lie) writes: Anyone know what function i can use to encrypt / decrypt field or record ? When record saved, it saved in encrypt format. When i need to read data, i just call decrypt function. I am using MS Access 2003 and Postgresql 8.3 I'd suggest referring to the fine manual... http://www.postgresql.org/docs/8.3/static/pgcrypto.html -- output = reverse("ofni.sailifa.ac" "@" "enworbbc") Christopher Browne "Bother," said Pooh, "Eeyore, ready two photon torpedoes and lock phasers on the Heffalump, Piglet, meet me in transporter room three" -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Need help with CASE statement in Function
Dear friends, I am a new user to postgreSQL and really need help to solve my "stupid ?" problem. I have created function with 4 arguments like this : CREATE OR REPLACE FUNCTION "public"."fHistoryCard" (begdate date, enddate date, ProductID varchar, storeID varchar) RETURNS SETOF "public"."tbltrans" AS $body$ /* New function body */ select * from tbltrans where tbltrans."Date" between $1 and $2 and upper(tbltrans."ProductID")=upper($3) and tbltrans."StoreID"=$4 order by tbltrans."Tanggal"; $body$ LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; - My Question is : How to make argument 4 optional ? When IS NULL the function will show all transaction between date $1 and $2 and product ID=$3 But when $4 is not null then the fucntion will show all transaction between date $1 and $2 and product ID=$3 AND STOREID=$4. I really appreciate any suggestions. Thanks a lot. Hengky
[SQL] Rule Error
Dear Friends,
I have problem with rule and tried several times to solve it but not yet
success. Hope someone can help me.
I have 2 tables : tblmasdbt and tblmasgl.
I want on every record insertion in tblmasdbt, that record also
automatically insert into tblmasdbt. I need only 2 related field.
So I create rule like this
--- SQL ---
CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt"
DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK));
But I always get this error :
-- ERROR MESSAGE --
ERROR: column "kodegl" of relation "tblmasgl" does not exist
Here is the Table Structure
---
CREATE TABLE "public"."tblmasgl" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"GOLONGAN" VARCHAR(10),
"AWAL" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
CONSTRAINT "tblmasgl_pkey" PRIMARY KEY("KODEGL"),
CONSTRAINT "tblmasgl_fk" FOREIGN KEY ("KODEGL")
REFERENCES "public"."tbltragl"("KODEGL")
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT DEFERRABLE
) WITHOUT OIDS;
CREATE TABLE "public"."tblmasdbt" (
"KODEGL" VARCHAR(15) NOT NULL,
"NAMAREK" VARCHAR(50),
"ALAMAT" VARCHAR(75),
"Telp" VARCHAR(50),
"Facs" VARCHAR(50),
"KOTA" VARCHAR(30),
"HP" VARCHAR(20),
"Plafond" DOUBLE PRECISION DEFAULT 0,
"Operator" VARCHAR(3),
"SALDOAWAL" DOUBLE PRECISION DEFAULT 0,
CONSTRAINT "tblmasdbt_pkey" PRIMARY KEY("KODEGL")
) WITHOUT OIDS;
Hope someone could help me. Thanks a lot
Re: [SQL] Rule Error
Yes, it works now ! Wow, the problem is in the field name. Changed it to lowercase solved the problem. Thank you to all ho give me this advice. But now I have another question regarding to this field, what command I can use in UPDATE RULE to make these 2 fields (KODEGL and NAMAREK) keep syncron between these 2 tables (tblmasdbt and tblmasgl) ? Thank you so much. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. Kretschmer Sent: 04 Oktober 2007 21:00 To: [email protected] Subject: Re: [SQL] Rule Error am Thu, dem 04.10.2007, um 19:22:32 +0800 mailte Hengky Lie folgendes: > CREATE RULE "rule1" AS ON INSERT TO "public"."tblmasdbt" > > DO (insert into tblmasgl (KODEGL,NAMAREK) VALUES (new.KODEGL, new.NAMAREK)); > > > > But I always get this error : > > > ERROR: column "kodegl" of relation "tblmasgl" does not exist > > > Here is the Table Structure > > CREATE TABLE "public"."tblmasgl" ( > > "KODEGL" VARCHAR(15) NOT NULL, Okay, you need to quote the column-name with " since they are in uppercase. DO (insert into tblmasgl ("KODEGL","NAMAREK") and maybe also new."KODEGL" and new."NAMAREK". Try it and tell if you have success. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Pls Hlp: SQL Problem
Dear friends, I have problem in postgresql view. This is the view (Pay attention to the red and blue colours): SELECT tblpenjualan.tanggal, tblpenjualan."operator", tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam, tblpenjualan.id, tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga, tbltransaksi.disc1, tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode, tblkategoriproduk.kategori, tbltransaksi.hargapokok * tbltransaksi.keluar AS modal, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric * (tbltransaksi.keluar * tbltransaksi.harga) AS jumlah, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric * (tbltransaksi.keluar * tbltransaksi.harga) - tbltransaksi.hargapokok * tbltransaksi.keluar AS profit FROM tblpenjualan JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text LEFT JOIN tblsubkategori ON tblproduk.subkat::text = tblsubkategori.id::text LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text = tblkategoriproduk.kode::text; The result is : "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPS HAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.;4200.;240. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"899100425";3.00;1650.00;0.00;"TAKE-IT MILK CHOCOLATE WAFER 17 G";"8";"BISKUIT";"MKN";"Makanan";4680.;4950.;270. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8991115000103";2.00;1600.00;0.00;"ALPENLIEBE ORIGINAL 41 G ISI 10 BTR";"62";"PERMEN";"MKN";"Makanan";3000.;3200.;200. "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8990800010533";1.00;1600.00;0.00;"MENTOS CHEWY DRAGEES 37 G";"62";"PERMEN";"MKN";"Makanan";1500.;1600.;100. Why the calculation result has so many decimals ? Thank you
Re: [SQL] Pls Hlp: SQL Problem
Dear Richard, Sorry for that :) and thanks for your tips, but it didn't solve my problem. Maybe because my postgresql knowledge limitation. I have tried your suggestion, my view like this : -- View: "vwlapjual" -- DROP VIEW vwlapjual; CREATE OR REPLACE VIEW vwlapjual AS SELECT tblpenjualan.tanggal, tblpenjualan."operator", tblpenjualan.noinvoice, tblpenjualan.bayar, tblpenjualan.bayarvocher, tblpenjualan.jam, tblpenjualan.id, tblpenjualan.jeniscard, tbltransaksi.kodeproduk, tbltransaksi.keluar, tbltransaksi.harga, tbltransaksi.disc1, tblproduk.namabarang, tblproduk.subkat, tblsubkategori.subkategori, tblkategoriproduk.kode, tblkategoriproduk.kategori, tbltransaksi.hargapokok * tbltransaksi.keluar::numeric(2,0) AS modal, tbltransaksi.keluar * tbltransaksi.harga - (tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar * tbltransaksi.harga))::numeric(10,0) AS jumlah, tbltransaksi.keluar * tbltransaksi.harga - tbltransaksi.disc1 / 100::numeric(3,0) * (tbltransaksi.keluar * tbltransaksi.harga) - (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(10,0) AS profit FROM tblpenjualan JOIN tbltransaksi ON tblpenjualan.id = tbltransaksi.jualid LEFT JOIN tblproduk ON tbltransaksi.kodeproduk::text = tblproduk.kode::text LEFT JOIN tblsubkategori ON tblproduk.subkat::text = tblsubkategori.id::text LEFT JOIN tblkategoriproduk ON tblsubkategori.kategoriid::text = tblkategoriproduk.kode::text; ALTER TABLE vwlapjual OWNER TO hpl; and the view result like this : "2008-07-20";"ULY";"01-200708";"Tunai";0.00;"1899-12-30 21:14:47+06:55:25";1;"";"8993417200021";1.00;4200.00;0.00;"ELIPS HAIR VITAMIN ";"121";"VITAMIN RAMBUT";"NF";"Non Food";3960.00;4200.;240. Thanks for any suggestion. Regards, Hengky Richard Huxton wrote: Hengky Lie wrote: Dear friends, Hello. Quick tip - don't reply to another message when starting a new thread, people will assume it's part of the old thread. [snip most of query] / 100::numeric * Food";3960.;*4200.*;*240.* Why the calculation result has so many decimals ? Because you've cast 100 to numeric, rather than numeric(10,2) or whatever. This means you get the full accuracy on the calculation - it won't ever trim a numeric (that's the whole reason to have the type).
[SQL] Simple Problem ?
Dear friends, I really amazed with this problem and need your help. I run simple query from Query window of PgAdmin3: SELECT * FROM mytable limit 10; and the result are NON UPDATABLE QUERY, while i need the result are updatable. I run the same query from Passthrough SQL in Microsoft Access, the result are the same (non updatable). But when i run this query from EMS SQLMANAGER for postgre, the result are UPDATABLE. I really amazed. Could someone help me ? Thanks a lot -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Simple Problem ?
I have to use sql command because i want to retrieve data according to several criteria. The SQL command just a sample to show that the data i retrieve not updateable. The real query like : SELECT * FROM TBLPRODUK WHERE SUBKAT='abc'; So i do not need to view data from data viewer, but using query with an updateable result. Any suggestion ? Thanks in advance Guillaume Lelarge wrote: Hengky Lie a écrit : This is the same as what i think but the table has primary key and has no relation to other table. This is the table definition : [...] These query result non updateable records : SELECT kode, namabarang from tblproduk; SELECT * from tblproduk; What is the problem ? Oops, I was wrong on this. You can't edit results in the query tool. You need to use the data viewer (menu Tools/View Data/View All Rows ; there's also a button in the toolbar). -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
