Re: [GENERAL] [SQL] 500KB PDF files for postgresql8.3, which is the most efficient way?
* Emi Lu wrote: > Solution: > > (1) Save pdfs to file system, only point file name in psql8.3 > > (2) Save oids of pdfs into table > > (3) Save pdf files as bytea column in psql8.3 (4) Put them into an object store, eg. venti. cu -- ---------- Enrico Weigelt, metux IT service -- http://www.metux.de/ phone: +49 36207 519931 email: weig...@metux.de mobile: +49 151 27565287 icq: 210169427 skype: nekrad666 -- Embedded-Linux / Portierung / Opensource-QM / Verteilte Systeme -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] faking writable views as tables
Hi folks, I'm using several writable views (-> update & insert rules) and got trouble with pgadmin - it refuses to insert/edit values since it sees the table as a view and believes views are never writable. So i've just set the reltype in pg_class to 'r'. The frontends now see it as a writeble table again, but when dropping the table, i've got a warning, that some file (seems to be /) could not be deleted. Is this the table storage file, which simply doesn't exist on views ? Are my changes to pg_class dangerous in any way ? cu -- --------- Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Foreign key to a view (UNION of two or more tables),
* Karl O. Pinc <[EMAIL PROTECTED]> wrote:
> So the problem then is that there are codes (e.g. cities) that are
> used by multiple questions, sometimes optional or N/A is allowed
> and sometimes not.
For such cases you could introduce another layer, like a datatype.
Each question can be answered with some datatype, and may optionally
be empty - just like rows in a database :).
CREATE TABLE q_types
(
id oid default nextval('q_type_id'),
description text,
..
);
CREATE TABLE q_type_values
(
type_id oid,
answer_id oid,
title text,
...,
PRIMARY KEY(type_id, answer_id)
);
CREATE TABLE q_question
(
id oid default nextval,
qtype oid references q_types (id),
questiontext
);
CREATE TABLE q_answer
(
user_id oid references q_user(id),
question_id oid references q_question(id),
value oid
);
...
Maintaining the integrity of q_answer.value is a little bit more
complicated. I don't know if its possible with an foreign key,
since it spans over multiple tables ( question_id->qtype + value )
You probably need an hand-written trigger.
Unanswered questions (or selected n/a) could be marked by simply
setting value to NULL. You also could introduce a separate flag
in q_answer for that.
BTW: its probably not such a bad idea to present the whole
stuff as one writable view to the frontend and let the database
do the logic of mapping answer texts <-> ids.
cu
--
---------
Enrico Weigelt== metux IT service
phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: [EMAIL PROTECTED]
-
Realtime Forex/Stock Exchange trading powered by postgresSQL :))
http://www.fxignal.net/
-
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] rule as on insert to view with multiple fk referencing the same table
* Joan Picanyol <[EMAIL PROTECTED]> wrote: > create table a (id serial not null primary key, data int); > create table b (id serial not null primary key, data int\ > ref1 int references a not null, ref2 int references a not null,\ > ref3 int references a); > create view vw (data_a, data_b1, data_b2, data_b3) as select \ > (b.data as data_b, a1.data as data_a1, a2.data as data_a2,\ > a3.data as data_a3) from b, a a1, a a2, a a3 where b.ref1=a1.id\ > and b.ref2=a2.id and b.ref3=a3.id; > > And now I want to create an insert rule into vw with all my foreign keys > properly set. I can't think of anything better that doing first the > three inserts into a and then use nextval()-1, nextval()-2 etc for the > fk values, but it looks really ugly (even though I could wrap up within > a BEGIN(...)COMMIT). You want the same ID in both tables ? This won't work, since nextval() isn't predictable in the way you want. (even if you *could* put it into an own transaction, it wont help) You probably could use nextval() only the first time and then currval(). If you've got your ID in your view, its probably easier (and more safe ?) to declare this ID field as default nextval(...) and just catch up this value in your rules. BTW: if you have created an ON SELECT DO INSTEAD rule, postgres treats the table as a view, and it wants you have all writing rules to be also INSTEAD. (bacause writing to a shadow table is useless ?). cu -- --------- Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] REFERENCES error message complaint, suggestion
* Tom Lane <[EMAIL PROTECTED]> [2004-03-04 11:24:11 -0500]: > Double quotes are for names (identifiers). Single quotes are > for string literals (constants). BTW: is this general SQL syntax or just PostgeSQL ? mysql does no distinction (which is IMHO very unclean), and it gets even worse since mysqldump's output does it exactly the wrong way: identifiers in '' and string constants in "" ... cu -- --------- Enrico Weigelt== metux IT services phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL-Zugang ab 0 Euro. -- statische IP -- UUCP -- Hosting -- - ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
