[SQL] Changing PL/pgSQL triggers
Hi, What's the easiest way to modify or view a function written in PL/pgSQL? I've been using pg_dump to get the original function, then dropping and creating the function and trigger after making a change. Is there an easier way? -James
[SQL] Referencing a view?
Hi,
Is there anyway that you can reference a column in
a view for referential integrity? The problem is with the unique thing,
obviously I can't create a unique index on a view. Here is what I
have:
CREATE SEQUENCE
"addresses_id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1 ;
CREATE TABLE "org_addresses" ( "id"
integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL, "orgid"
integer references orgs on delete cascade, "name" character
varying(255), "street1" character varying(255), "street2"
character varying(100), "city" character varying(100), "state"
character(2), "zip" character(10), Constraint
"org_addresses_pkey" Primary Key ("id"));
CREATE TABLE "user_addresses" ( "id"
integer DEFAULT nextval('addresses_id_seq'::text) NOT NULL, "userid"
integer references users on delete cascade, "name" character
varying(255), "street1" character varying(255), "street2"
character varying(100), "city" character varying(100), "state"
character(2), "zip" character(10), Constraint
"user_addresses_pkey" Primary Key ("id"));
CREATE VIEW "addresses" as SELECT
user_addresses.id, user_addresses.userid, user_addresses.name,
user_addresses.street1, user_addresses.street2, user_addresses.city,
user_addresses.state, user_addresses.zip FROM user_addresses UNION SELECT
org_addresses.id, NULL::unknown, org_addresses.name, org_addresses.street1,
org_addresses.street2, org_addresses.city, org_addresses.state,
org_addresses.zip FROM org_addresses;
So this gives me a view with every address, each
with a unique id as I used the same sequence in both tables. Now what I
want to do is something like this :
CREATE TABLE orders (
id serial primary
key,
shipping_address int references
addresses(id),
.
.
);
Which of course doesn't work because
addresses as a view can't have a unique index. Any way around
this?
- James
Re: [SQL] Referencing a view?
Thanks for all the responses! The one from Grigoriy was particularly interesting, I hadn't thought of that approach. However, I came to the realization that if somebody changes their address, I don't want it to be changed on previous orders. So I think i'll change the orders table to contain the actual address information and use an INSERT ... SELECT instead. That way I can be sure I have an accurate record of all orders. Thanks! - James ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Help with SQL statement - Thanks
SELECT * FROM Payments P1 WHERE Date = (SELECT MAX(Date) FROM Payments P2 WHERE P2.CustomerNo = P1.CustomerNo) I think that will do it. - James - Original Message - From: "Henry" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]@postgresql.org> Sent: Saturday, July 14, 2001 3:16 PM Subject: [SQL] Help with SQL statement - Thanks > There is a table called "Payments" which records every payment that a > company has received: > > CustomerNo DateAmount > > 32142/1230 > 32144/1050 > 99434/1840 > 99435/1030 > 99432/1370 > 11215/2310 > 11214/2040 > 11213/1230 > (more data...) > > > I want to be able to pull out the last payment made by everyone in a query: > > CustomerNo DateAmount > --- > 32144/1050 > 99435/1030 > 11215/2310 > (other users...) > > How should I write the SQL statement? Please email to me at > [EMAIL PROTECTED] Thank you very much. > > Henry > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] transposing data for a view
- Original Message - From: "H Jeremy Bockholt" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 30, 2001 7:36 PM Subject: [SQL] transposing data for a view > I have a generalized table: > > scanid | region | volume > - > 1 A 34.4 > 1 B 32.1 > 1 C 29.1 > 2 A 32.4 > 2 B 33.2 > 2 C 35.6 > . > . > . > > I want to create a flattened out view that looks like the following: > > scanid | A_volume | B_volume | C_volume > > 134.4 32.1 29.1 > 232.4 33.2 35.6 > . > . > . > > How do I correctly/efficiently construct a psql query to > pivot/transpose the data? I am using postgreSQL version 7.0.x SELECT A.scanid, A.volume AS A_volume, B.volume AS B_volume, C.volume AS C_volume FROM table A JOIN table B ON (A.scanid = B.scanid) JOIN table C ON (B.scanid = C.scanid) WHERE A.region = 'A' AND B.region = 'B' AND C.region = 'C' - James ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Aggregate binary AND
Hi, Is there an aggregate binary AND function in postgres? If not, is there a way to write your own aggregate functions? Examples? - James ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] transposing data for a view
> > BTW, I don't believe the self-join approach proposed > > earlier will work, because joining on "scanid" will > > create a cartesian type join where the region values > > will be duplicated (multiplicated!). > > Not if you're talking about my query, they won't. I use that query form > in many projects to create roll-ups; it's the "best" SQL92 approach to > the "pivot table" problem. However, it will not work in 7.0.3. I think he might be talking about mine. The region values will not be duplicated, the WHERE clause prevents it. I kind of prefer my own query aesthetically, is it as efficient internally? - James ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Search by longitude/latitude
Here is some perl code which does what you want. - Original Message - From: "Chris Ruprecht" <[EMAIL PROTECTED]> To: "pgsql" <[EMAIL PROTECTED]> Sent: Monday, October 08, 2001 8:58 AM Subject: [SQL] Search by longitude/latitude > Hi all, > > I need to implement "Find all hospitals in a 5 mile radius". Say I have all > the coordinates on them stored in a table with the fields longitude and > latitude. Has anybody some sample code for that? > > Best regards, > Chris > > > > _ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > distance Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Editor for pgsql
On Monday 22 July 2002 12:27 pm, Josh Berkus wrote: > Me, I use Kate, an MDI text editor from the KDE crew, and CVS for > version control. Thanks, KDE guys!But, after 3 years of Postgres, > I'm pretty fluent in PL/pgSQL. I even double-quote without thinking > about it. How do you use CVS on your database? I recently started doing this, and i'm wondering how other people handle it. Basically I create a sql folder with three sub-folders tables, views and functions. I have a file for each table in tables, each view in views and for each trigger and/or function in functions. For the actual editing? I'm a vi fan myself :). If i'm using the graphical vim I can even do CVS operations with a custom menu. - James ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
