Re: [GENERAL] Pl/PgSQL String formatting like raise?
Pavel, thank you very much for your answer. -- Best Regards, Csaba Együd there is nothing similar. You should to use module plvsubst from Orafce http://www.postgres.cz/index.php/Oracle_functionality_(en)#PLVsubst regards Pavel Stehule 2009/2/11 Csaba Együd : Hi, is there a way in a Pl/PgSQL function (PG 8.1.* / 8.3.*) to cut together a string value like it is done for RAISE Note/Exception? I'd like to use s.g. like this : StrVar := 'select * from t1 where f1=% and f2=%', quote_literal(Param1),quote_literal(Param2); Many Thanks! csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pl/PgSQL String formatting like raise?
Hi, is there a way in a Pl/PgSQL function (PG 8.1.* / 8.3.*) to cut together a string value like it is done for RAISE Note/Exception? I'd like to use s.g. like this : StrVar := 'select * from t1 where f1=% and f2=%', quote_literal(Param1),quote_literal(Param2); Many Thanks! csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database schema & data synchronizer software for PostgreSQL?
Hi, I'd like to ask your suggestions about a reliable admin software which is able to compare two dabases and generate a schema synchrinizer script. It would be nice to be able to generate data synchronization script for only the selected tables, and other features. Thank you, -- Best Regards, Csaba Együd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
"Richard Huxton" <[EMAIL PROTECTED]> a következőket írta üzenetében news:[EMAIL PROTECTED] Csaba Együd wrote: Thx for your reply. Is there any possible way to generate an sql to "copy" these defaults to the view. Nothing pre-packaged that I know of. You could probably do something copying values about in pg_attribute and pg_constraint, but that'd be an "at your own risk" sort of activity I suspect. For simpler defaults etc. you could probably get what you need from information_schema.columns -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Or use of coalesce() function??? I mean sg like: ..., qtyunitid = coalesce(NEW.qtyunitid, -1), ... in the Rule def. -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
""Grzegorz Jaśkiewicz"" <[EMAIL PROTECTED]> a következőket írta üzenetében news:[EMAIL PROTECTED] maybe that constraint ?? CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE, Also, that table seem to be far away from perfect, too many fields, you should chop it into few smaller tables. 2008/12/5 Csaba Együd <[EMAIL PROTECTED]>: Hi, thx for your reply too. Not that I guess because there is a default row in qtyunits with id=-1. Too many fields: How would you chop this table? thx, -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
"Richard Huxton" <[EMAIL PROTECTED]> a következőket írta üzenetében news:[EMAIL PROTECTED] Csaba Együd wrote: Hi, I have problems with inserting rows into an updatable view through it's insert rule. [snip] But in the table definition I defined DEFULT=(-1) for this field. What's going wrong? Shouldn't it inherit these settings from the table? Maybe, but it doesn't (and I think I've seen someone arguing it shouldn't). You can manually apply constraints/defaults etc. to the view though. I can't recall if you do ALTER VIEW view_products_1 or ALTER TABLE view_products_1, but it's one of them. -- Richard Huxton Archonet Ltd Richard, Thx for your reply. Is there any possible way to generate an sql to "copy" these defaults to the view. --Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updatable Views - DEFAULT doesn't inherit from table???
Hi,
I have problems with inserting rows into an updatable view through it's
insert rule.
Running this:
insert into view_products_1
(id,firmid,name_en,name_hu,artnum1,artnum2,description_hu,description_en,pkgunitid,minpkg,customstariff,vat)
values
('23','1','','','dd','','d','dd','1','10','200','20')
the engine sends this error:
ERROR: null value in column "qtyunitid" violates not-null constraint
** Error **
ERROR: null value in column "qtyunitid" violates not-null constraint
SQL state: 23502
But in the table definition I defined DEFULT=(-1) for this field. What's
going wrong? Shouldn't it inherit these settings from the table?
Many thanks,
--
Best Regards,
Csaba Együd
IN-FO Studio
Here is the table:
---
CREATE TABLE whm.products
(
id serial NOT NULL,
firmid integer NOT NULL,
name_en character varying(250) NOT NULL DEFAULT ''::character varying,
name_hu character varying(250) NOT NULL DEFAULT ''::character varying,
artnum1 character varying(250) NOT NULL,
artnum2 character varying(250) NOT NULL DEFAULT ''::character varying,
description_hu character varying(512) NOT NULL DEFAULT ''::character
varying,
createtime timestamp with time zone NOT NULL DEFAULT now(),
"createuser" name NOT NULL DEFAULT "session_user"(),
lastmodtime timestamp with time zone NOT NULL DEFAULT now(),
lastmoduser name NOT NULL DEFAULT "session_user"(),
description_en character varying(512) NOT NULL DEFAULT ''::character
varying,
qtyunitid integer NOT NULL DEFAULT (-1),
pkgunitid integer NOT NULL DEFAULT (-1),
minpkg integer NOT NULL DEFAULT 0,
customstariff character varying(64) NOT NULL DEFAULT ''::character
varying,
vat numeric NOT NULL DEFAULT 20,
service boolean NOT NULL DEFAULT false,
notes character varying(512) DEFAULT ''::character varying,
CONSTRAINT pk_products_id PRIMARY KEY (id),
CONSTRAINT fk_products_firmid FOREIGN KEY (firmid) REFERENCES whm.firms
(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_products_qtyunitid FOREIGN KEY (qtyunitid) REFERENCES
whm.qtyunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT products_pkgunitid FOREIGN KEY (pkgunitid) REFERENCES
whm.pkgunits (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
And here is the definition of the view:
CREATE OR REPLACE VIEW whm.view_products_1 AS
SELECT products.id, products.firmid, products.name_en, products.name_hu,
products.artnum1, products.artnum2, products.description_hu,
products.createtime, products.createuser, products.lastmodtime,
products.lastmoduser, products.description_en, products.qtyunitid,
products.pkgunitid, products.minpkg, products.customstariff, products.vat,
products.service, products.notes FROM whm.products WHERE products.firmid =
1;
CREATE OR REPLACE RULE view_products_1_insert AS
ON INSERT TO whm.view_products_1 DO INSTEAD INSERT INTO whm.products
(firmid, name_en, name_hu, artnum1, artnum2, description_hu, description_en,
qtyunitid, pkgunitid, minpkg, customstariff, vat, service, notes)
VALUES (1, new.name_en, new.name_hu, new.artnum1, new.artnum2,
new.description_hu, new.description_en, new.qtyunitid, new.pkgunitid,
new.minpkg, new.customstariff, new.vat, new.service, new.notes);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to define automatic filter condition?
Sure, no problem. Please avoid top-posting. Regards, Andreas -- Great, thanks. -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to define automatic filter condition?
Andreas, thank you for your reply. Yes, I know that there is such a mathod but I read somewhere that it is just a limited way. BTW, I am going to dig into this more deeper. A possible solution has come into my minde: does it have any risk to use a read only view for selects but inserts and updates are performed on the base table? I mean that I would select data from the view and show that automatically filtered row set to the user but when she/he inserts or updates a row the generated query would operate on the real (base) table. thx, -- Csaba ""A. Kretschmer"" <[EMAIL PROTECTED]> a következoket írta üzenetében news:[EMAIL PROTECTED] am Wed, dem 12.11.2008, um 8:08:08 +0100 mailte Csaba Együd folgendes: Hi All, --PG8.3 --Windows 2k3 SBS I would like to apply an automatic filter condition to a table. I create a TEMP table at the beginning of the session to store a value to build up a filter condition and I would like to apply this condition to every select statement to a table during the current session. I know that I could use views but views are read only and I also need Update and Insert. You can define RULEs on insert and update on a view to do real insert/update on the base-table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to define automatic filter condition?
Hi All, --PG8.3 --Windows 2k3 SBS I would like to apply an automatic filter condition to a table. I create a TEMP table at the beginning of the session to store a value to build up a filter condition and I would like to apply this condition to every select statement to a table during the current session. I know that I could use views but views are read only and I also need Update and Insert. Could anybody turn my head to the right direction? Thank you, -- Csaba -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
