[SQL] How to build this field
Hi everybody I need to build an additional field (metakey) out of three fields in the table. SELECT a.a_kurzbez, a.a_ausgabenr, a.a_bundnr, a.a_nr, a.a_z_blaenge, a.a_z_laenge, a.a_z_umfang FROM auftrag a should be changed to something like SELECT (a.a_kurzbez + a.a_ausgabenr + a.a_bundnr) as metakey, a.a_kurzbez, a.a_ausgabenr, a.a_bundnr, a.a_nr, a.a_z_blaenge, a.a_z_laenge, a.a_z_umfang FROM auftrag a output : field : metakeya_kurzbez a_ausgabenr a_bundnr DMD 001 03 DMD 001 03 Thanks for any help ... jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Wed, Mar 07, 2001 at 08:38:25AM -0800, Josh Berkus wrote: > Thomas, Karel, > > > I agree with Karel's point that it may be a pain to use a procedural > > language to manipulate a "stringy" interval value. If you use a C > > function instead, you can get access to the internal manipulation > > functions already present, as well as access to system functions to > > manipulate a tm structure. > > Ah, but this leaves out two important considerations of my particular > problem: > > 1. The interval I want to manipulate is limited to a relative handful of > possibilities (it's a billing interval): 1 week, 2 weeks, 2x/Month, 1 > month, 2 months, 3 months. > > 2. I don't do C. And I don't have the budget to hire somebody to di it > in C. > > If this was a bigger budget project, I'd simply take Karel's notes and > hire a programmer to create the to_char(Interval) function and thus > contribute to PostgreSQL ... but this project is over budget and behind > schedule already. Now I'm not writing to_char(interval), because current source (7.1) is freeze for new features and I'm waiting for 7.2 devel. cycle and I'm spending time with other things (PL/Python, the Mape project etc..). If it's *really important* for you I can write it next week(s), ... of course, my time is limited :-) May be try found some other solution. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to build this field
Hi Juerg, > I need to build an additional field (metakey) out of three fields in > the table. > > SELECT a.a_kurzbez, a.a_ausgabenr, a.a_bundnr, a.a_nr, a.a_z_blaenge, > a.a_z_laenge, a.a_z_umfang FROM auftrag a > > should be changed to something like > > SELECT (a.a_kurzbez + a.a_ausgabenr + a.a_bundnr) as metakey, > a.a_kurzbez, a.a_ausgabenr, a.a_bundnr, a.a_nr, a.a_z_blaenge, > a.a_z_laenge, a.a_z_umfang FROM auftrag a Try the following: SELECT a.a_kurzbez || ' ' || a.a_ausgabenr::text || ' ' || a.a_bundnr::text as metakey, ... remainder as above or, if you need that frequently, create a function: CREATE FUNCTION metakey (text, int4, int4) returns text as ' SELECT $1 || ' ' || text($2) || ' ' || text($3) ' LANGUAGE 'sql'; || is the "concatenate text" operator. Change the param types and cast them as you need. Hope that helps, Christof. > output : > > field : metakeya_kurzbez a_ausgabenr > a_bundnr > > DMD 001 03 DMD 001 > 03 -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Buglet?
Christopher Sawtell <[EMAIL PROTECTED]> writes: > It that a bug? You could argue that. The handling of NULL arrays (not to mention NULLs in arrays) is pretty brain-d^H^H^H^H^H^Hsimplistic; I don't think the original designer thought about NULLs at all. All of the array code could use an overhaul, actually, to move it out of the student-project category and into the professional-tool category. I've taken occasional swipes at it over the past couple of years, but it still leaves a lot to be desired. Any volunteers out there? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How to drop an trigger
I've created quite a few foreign key constraints in the database that I
am currently working on, and now that I've altered the structure and
dropped a table that had a foreign key reference to a couple of other
tables, I need to get rid of those foreign keys (they weren't dropped
automagically with the table), as I get errors on trying to update those
tables.
Trouble is that the foreign keys show up in a schema dump as
triggers (AFAIK there is no other way to display foreign key
constraints) which I don't know how to drop. Here's an example:
\connect - frank
--
-- TOC Entry ID 56 (OID 52367)
--
-- Name: "RI_ConstraintTrigger_52366" Type: TRIGGER Owner: frank
--
CREATE CONSTRAINT TRIGGER "" AFTER DELETE ON "index" NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_cascade_del" ('', 'legende', 'index', 'UNSPECIFIED',
'platz', 'id');
I tried dropping it with
=# drop trigger RI_ConstraintTrigger_52366 on index;
which fails with
ERROR: DropTrigger: there is no trigger ri_constrainttrigger_52366 on
relation index
What to do? And more broadly, what's the recommended way to deal with
this in general? Avoid creating triggers by always creating named
foreign keys with something like
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address)
REFERENCES addresses(address) MATCH FULL;
(from Bruce's book)?
Regards, Frank
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: How to drop an trigger
On Thu, Mar 08, 2001 at 04:42:46PM +0100, Frank Joerdens wrote: > I've created quite a few foreign key constraints in the database that I > am currently working on, and now that I've altered the structure and > dropped a table that had a foreign key reference to a couple of other > tables, I need to get rid of those foreign keys (they weren't dropped > automagically with the table), as I get errors on trying to update those > tables. Just an idea: Is it safe to just delete the corresponding row in pg_trigger? Regards, Frank ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] quotes in pl/pgsql 0n variable type text or varchar
Roberto Mello wrote: > On Thu, Mar 08, 2001 at 05:54:38PM -0500, Najm Hashmi wrote: > > Hi all, I just want to know how to put quotes around a string. Is there a > > function to do so? > > If not how can I escape a single quote. > > Others have answered how to quote a single string. I wrote an entire > section on quotes on the PL/SQL-to-PL/pgSQL-HOWTO. It's called "Quote me > on that" and mentions several cases on quote usage in PL/pgSQL and what to > do about it. > http://www.brasileiro.net/roberto/howto > > -Roberto > > -- > +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ > Roberto Mello - Computer Science, USU - http://www.brasileiro.net > http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer > DOS = Damned Old Software Hi, I just want to know is there way to put single quotes around the value of var type text or varchar. Thanks in advance. Regards. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: How to drop an trigger
On Thu, Mar 08, 2001 at 05:06:29PM +0100, Frank Joerdens wrote: > On Thu, Mar 08, 2001 at 04:42:46PM +0100, Frank Joerdens wrote: > > I've created quite a few foreign key constraints in the database that I > > am currently working on, and now that I've altered the structure and > > dropped a table that had a foreign key reference to a couple of other > > tables, I need to get rid of those foreign keys (they weren't dropped > > automagically with the table), as I get errors on trying to update those > > tables. > > Just an idea: Is it safe to just delete the corresponding row in > pg_trigger? No, it ain't: After deleting the rows corresponding to the offending triggers in pg_trigger, I can't vacuum, or dump. - Frank ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Writing SQL functions in Postgres
Hi, I want to write an SQL function in postgres that returns row as a result. The problem is the select statement inside the funtion has a two table join. So I dont know what to put after SETOF CREATE FUNCTION dummy() RETURNS SETOF ? AS 'select a.name,b.cc from tblusers a, tbldocs b where a.name=b.name' LANGUAGE 'SQL'; SETOF tblusers -- doesnt work ERROR: function declared to return type tblusers does not retrieve (tblusers.*) neither does SETOF tbldocs SETOF tblusers,tbldocs wont work either. Anyone was able to returns a row that is a result of few table joins inside the function??? Regards, Boulat Khakimov -- Nothing Like the Sun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to build this field
Josh, > > [ stuff deleted ] > > CREATE FUNCTION metakey (text, int4, int4) returns text as > > ' SELECT $1 || ' ' || text($2) || ' ' || text($3) ' > > LANGUAGE 'sql'; > > > > || is the "concatenate text" operator. > > > > Change the param types and cast them as you need. > > ... But keep in mind that if any of the above values are NULL, the > whole expression will be NULL. If one or more of the columns allows > NULLs, you will need a function that includes testing for NULL. > > -Josh Berkus Thanks for the hint. COALESCE is your friend, then :) Christof. -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: How to drop an trigger
Frank Joerdens <[EMAIL PROTECTED]> writes: >> Just an idea: Is it safe to just delete the corresponding row in >> pg_trigger? > No, it ain't: After deleting the rows corresponding to the offending > triggers in pg_trigger, I can't vacuum, or dump. You need to adjust the reltriggers counts in the associated pg_class entries, too. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
karel,
> Now I'm not writing to_char(interval), because current source (7.1)
> is
> freeze for new features and I'm waiting for 7.2 devel. cycle and I'm
> spending
> time with other things (PL/Python, the Mape project etc..).
>
> If it's *really important* for you I can write it next week(s),
> ... of course, my time is limited :-)
Hey, if I wanted that, there'd be a consulting fee involved, hey?
Actually, I just changed the field to VARCHAR and provided a limited
range of options. Since there is not to_char('7 +00:00:00') yet, but
interval('1 month') works great, it makes more sense to store my data as
text.
Since I'm not writing the temporary interval2char function, I'll
mention that it seemed to me that it could be broken down into a series
of IF ... THEN statements either testing DATEPART or against other
INTERVAL values. A string could be built against the components of the
Interval.
Now, two follow-up questions:
1. Does ALTER TABLE in 7.1 beta 4 allow DROP COLUMN? I can't seem to
get it to work.
2. Has anyone given thought to a VB-style SELECT CASE (which we should
call 'SELECT MATCH') statement in PL/pgSQL? Different from the CASE
that allows you to select column values in the SELECT clause, SELECT
MATCH would be an IF ... THEN style structure offering an indefinite
numebr of options. I'm sure that PL/SQL has something like this ...
I'll look it up later today.
-Josh Berkus
__AGLIO DATABASE SOLUTIONS___
Josh Berkus
Complete information technology [EMAIL PROTECTED]
and data management solutions (415) 565-7293
for law firms, small businessesfax 621-2533
and non-profit organizations. San Francisco
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Writing SQL functions in Postgres
Boulat Khakimov <[EMAIL PROTECTED]> said: > I want to write an SQL function in postgres that returns > row as a result. > > The problem is the select statement inside the funtion has > a two table join. So I dont know what to put after SETOF > > CREATE FUNCTION dummy() > RETURNS SETOF ? > AS 'select a.name,b.cc > from tblusers a, > tbldocs b > where a.name=b.name' > LANGUAGE 'SQL'; > > > SETOF tblusers -- doesnt work > ERROR: function declared to return type tblusers does not retrieve > (tblusers.*) > > neither does SETOF tbldocs > > SETOF tblusers,tbldocs wont work either. There's good news and bad news. The good news is that if you define a view "tblboth" that selects from your two tables you can then do "returns setof tblboth". The bad news is that your function won't return a set of records - you'll get a list of OIDs (at least I think they're OIDs). Check the mailing archives for more on this. You can do your example with a simple view, but if you want a parameterised view you'll have to wait until 7.2 (I think it's on the todo list) - Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Access tables inside pl/pgsql functions
I would like to create a pl/pgsql function that can select from a table even though users can't select from the table directly. For example, create a table and function that hits the table as the postgres user. Log in as another user and select function_name();. This fails because the user does not have permissions to select from the table. The issues is that I don't want the users to be able to select from the table but I would like to allow the user to call a stored procedure than can select from the table. Any idea how to do this? Any help is greatly appreciated. Thanks, Michael Here is an example. Create the following table and function as the postgres user: CREATE TABLE tst ( tmp_relname name, id smallint ); CREATE FUNCTION test() RETURNS int AS ' BEGIN DELETE FROM tst; return 1; END;' LANGUAGE 'plpgsql'; Login as another user Select test(); -- this will fail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] explain EXPLAIN?
Hello, I'm looking for a better tutorial of how EXPLAIN works. I know Mr. Tom Lane wrote a "quick & dirty explanation" and that "plan-reading is an art that deserves a tutorial, and I haven't had time to write one". In which case I'd like to know if there's any other tutorials/resources. I think I get the jist of it (an index scan is better than a seq scan?) but I'd like to read more. Does anybody have any suggestions? -- Dave ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] A query that doesn't work on 7.1
Tom Lane wrote:
> Here's another twist though. Is this a bug
too or is this just beyond our reach?
> psql:lead1.sql:64: ERROR: Unable to select an aggregate function
avg(date)
It's just that we don't have any avg() function for date --- nor for
timestamp, which is a little more surprising.
FYI:
I got by with kind of a pseudo average (mean, I guess) for now implemented
as:
min(date) + (max(date) - min(date)/2)
You could probably gin up a usable avg(timestamp) using the avg(float8)
routines, since a timestamp is really just a double under the hood.
When you say "gin up" are you talking about C, PL/XXX, or just casts?
BTW, here's another question:
Here's a view of a union. The two selects are fast when executed
individually (and explain confirms that they use indexes). When I
query the view, though, it is slow (explain says the scans are sequential).
Is this expected or a bug?
-- Simulate a table of lead times
create view vend_v_lead as select
p.pnum as pnum,'vend' as type,ldtime::float8
as lead,0 as aging from vend_price v, prd_part p where v.pnum = p.pnum
union select
p.pnum,'hist',date_part('day',(m.tdate::datetime-m.mtr_date::datetime)::timespan),0
from mtr_reg m, prd_part p where m.pnum = p.pnum and m.ttype = 'po' and
m.status = 'clsd'
;
begin:vcard
n:Bateman;Kyle
x-mozilla-html:FALSE
org:Action Target Inc.
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
x-mozilla-cpt:;0
fn:Kyle Bateman
end:vcard
---(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] A query that doesn't work on 7.1
Kyle <[EMAIL PROTECTED]> writes: >> You could probably gin up a usable avg(timestamp) using the avg(float8) >> routines, since a timestamp is really just a double under the hood. > When you say "gin up" are you talking about C, PL/XXX, or just casts? I was thinking of full-scale cheating: make a new pg_aggregate entry for avg(timestamp) that points at the existing pg_proc entries that support avg(float8). CREATE AGGREGATE won't do this for you, but there's always good old INSERT INTO ... > Here's a view of a union. The two selects are fast when executed > individually (and explain confirms that they use indexes). When I > query the view, though, it is slow (explain says the scans are > sequential). > Is this expected or a bug? Seems odd. Need enough info to reproduce, please? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] perl dbd
my apologies if this is not the coreect list but I cannot seem to install the package DBD-Pg-0.73-1.i386.rpm it complains that it needs libpq.so.1 i have the following installed from a source package rebuild: postgresql-7.0.3-2 ..server ..devel ..perl ..tk ..odbc ..tcl thanks as always Ken ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] List Concatination
Folks, I have an interesting problem. For purpose of presentation to users, I'd like to concatinate a list of VARCHAR values from a subtable. To simplify my actual situation: CREATE TABLE clients ( client_id SERIAL PRIMARY KEY, client_name VARCHAR(50) ); CREATE TABLE client_contacts ( contact_id SERIAL PRIMARY KEY, client_id REFERENCES clients(client_id), first_name VARCHAR(25), last_name VARCHAR(25) ); CLients: 1 McMurphy Assoc. 2 Ross Construction Contacts 1 1 Jim Henson 2 1 Pat O'Gannet 3 2 SaraVaugn 3 2 BillMurray etc. What I'd like to be able to do is present a list of clients and their comma-seperated contacts in paragraph form, hence: Client Contacts McMurphy Assoc. Jim Henson, Pat O'Gannet, Kelley Moore Ross Construction Sara Vaugn, Bill Murray, Peter Frump, Siskel Ebert I can figure out how to do this procedurally (using PL/pgSQL and a cursor) but not how to do it declaratively (using only SQL). The reason it's important to do it declaritively is that there are actually two sub-tables with thousands of entries and the procedural approach is rather slow. Suggestions? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
