[SQL] Joined deletes but one table being a subquery.
Hi Folks, DELETE from eyp_listing where userid=t_a.userid and category_id=t_a.category_id; such queries work perfectly. but if t_a is a subquery how to accomplish the delete. Regds Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Joined deletes but one table being a subquery.
Hi Folks, DELETE from eyp_listing where userid=t_a.userid and category_id=t_a.category_id; such queries work perfectly. but if t_a is a subquery how to accomplish the delete. What kind of subquery it is? Exist/Not exist doesn't work? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Bug on parameter bigint in PL/PGSQL
I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8) and call the function from psql an error message which says that "the functioname(bigint) doesn't exist" is displayed. If i turn the int8 to int4 all works fine ... Now i use two int4 instead of one int8: is advisable? ciao, Michele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "SELECT IN" Still Broken in 7.4b
> What is measured by the \timing option? The figures reported > are slightly larger than those loged when the log_duration parameter > is true. The time of the psql client. It will include round trip activity including network overhead. signature.asc Description: This is a digitally signed message part
Re: [SQL] "SELECT IN" Still Broken in 7.4b
Bertrand Petit <[EMAIL PROTECTED]> writes: > On Wed, Aug 20, 2003 at 04:32:19PM -0400, Tom Lane wrote: >> against actual elapsed time (cf psql's \timing option) would tell. > What is measured by the \timing option? Elapsed time ... as seen by the client, of course. > The figures reported > are slightly larger than those loged when the log_duration parameter > is true. Yeah, that's what I'd expect, especially if there is a network hop involved. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] date calculation
Tomasz Myrta <[EMAIL PROTECTED]> writes:
> cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval)
> Does anyone know better way to cast it?
The above will probably give the wrong answer (off by your timezone
offset). The reference point should be zero hour GMT, but the first
cast will give zero hour local time. You should cast the reference
to timestamp with time zone (timestamptz) instead.
Also, I'd suggest using float-times-interval as a simpler and more
efficient way of forming the offset interval. So:
select 'epoch'::timestamp with time zone + your_ticks * '1 second'::interval;
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Bug on parameter bigint in PL/PGSQL
On Fri, 22 Aug 2003, Michele Bendazzoli wrote: > I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8) > and call the function from psql an error message which says that "the > functioname(bigint) doesn't exist" is displayed. > If i turn the int8 to int4 all works fine ... I can't reproduce from the information above. Integer constants are treated as int4, but it should be able to cast that to bigint. What is the function you are using to test and what version? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Bug on parameter bigint in PL/PGSQL
On Friday 22 August 2003 12:59, Michele Bendazzoli wrote: > I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8) > and call the function from psql an error message which says that "the > functioname(bigint) doesn't exist" is displayed. > If i turn the int8 to int4 all works fine ... Seems ok here: CREATE FUNCTION my_bigint_test(int8) RETURNS int8 AS ' BEGIN RETURN $1 + 1::int8; END; ' LANGUAGE 'plpgsql'; SELECT my_bigint_test(1); CREATE FUNCTION my_bigint_test 2 (1 row) SELECT my_bigint_test(2::int8); my_bigint_test 3 (1 row) Can you provide an actual example? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] "SELECT IN" Still Broken in 7.4b
On Fri, Aug 22, 2003 at 08:50:15AM -0400, Tom Lane wrote:
> Bertrand Petit <[EMAIL PROTECTED]> writes:
> > On Wed, Aug 20, 2003 at 04:32:19PM -0400, Tom Lane wrote:
> >> against actual elapsed time (cf psql's \timing option) would tell.
>
> > What is measured by the \timing option?
>
> Elapsed time ... as seen by the client, of course.
Is there a way to obtain the CPU time consumed by a query,
including a breakdown between user- and kernel-land?
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Bug on parameter bigint in PL/PGSQL
On Fri, 2003-08-22 at 15:05, Richard Huxton wrote: > On Friday 22 August 2003 12:59, Michele Bendazzoli wrote: > > I think i found a bug in PL/PGSQL: when i use a parameter bigint (int8) > > and call the function from psql an error message which says that "the > > functioname(bigint) doesn't exist" is displayed. > > If i turn the int8 to int4 all works fine ... > > Seems ok here: cut > Can you provide an actual example? I delete the example because i don't need it anymore (it was a function that simply get a int8 and return the last 4 byte (a int4), but now i don't use int8 anymore) and the one I try to reproduce now just works fine ... Maybe it was only my mistake due to the overload capacity of PL/PGSQL: i wonder if I was testing the errate version of the function while i was modifying another: the power of postgresql :-) Thank anyway. ciao, Michele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] composite type in a table
is there any way to use a composite type in a table? here's an example: say i want to create a type to hold currency: create type currency_type as ( base_objid int, base_amt decimal, conversion_rate decimal, converted_objid int ); i'd like to be able to define a column in a table of type currency_type: create table currency_table ( myMoney currency_type ); unfortunately, i get: ERROR: Attribute "mymoney" has composite type currency_type it sure would be nice if it was as simple as this, but it isn't. what's the easiest way to create a composite-like type that can be added to a table? also, how does one access the internal fields of a composite type? Regards, Floyd Shackelford VOICE: 334.735.9428 FAX: 702.995.6462 EMAIL: [EMAIL PROTECTED] ICQ #: 161371538 PGP Key ID: 0x2E84F2F2 PGP Fone at private.fwshackelford.com on request Shackelford Motto: ACTA NON VERBA - Actions, not words Alabama State Motto: AUDEMUS JURA NOSTRA DEFENDERE - We Dare Defend Our Rights The Philosophy of Liberty: http://www.isil.org/resources/introduction.swf "Just because you do not take an interest in politics doesn't mean politics won't take an interest in you." -- Pericles (430 B.C.) "I cannot undertake to lay my finger on that article of the Constitution which granted a right to Congress of expending, on objects of benevolence, the money of their constituents" --James Madison ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Delete denied?
Stephan, Tom: Hey, I have a function which can involve some records being deleted at the end. The user calling the function has permission to delete records (and I've tested this), but when I run the function I get "permission denied". Any clue? I remember in 7.1.x that you couldn't delete records in a function, but this is on 7.2.4. Contributing could be that the table holding the deleted records is a child table with an "ON CASCADE DELETE" of one of the tables being updated elsewhere in the function. And the function works if called by the owner of the table (and the function). -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Delete denied?
Guys: > Contributing could be that the table holding the deleted records is a child > table with an "ON CASCADE DELETE" of one of the tables being updated > elsewhere in the function. And the function works if called by the owner of > the table (and the function). Further update: I tried changing the owner of the function and table to no avail. Also tried dropping the FK. It seems to work if called by the owner of the database, but not otherwise. I'm really baffled ... I've written several hundred procedures for 7.2.4, and have never seen anything like this. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Delete denied?
Josh Berkus <[EMAIL PROTECTED]> writes: > Contributing could be that the table holding the deleted records is a child > table with an "ON CASCADE DELETE" of one of the tables being updated > elsewhere in the function. And the function works if called by the owner of > the table (and the function). I seem to recall that the original coding of the RI triggers was careless about executing the RI operations as the "right" user (namely the table owner). This very possibly was still broken in 7.2.4. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Delete denied?
On Fri, 22 Aug 2003, Josh Berkus wrote: > Guys: > > > Contributing could be that the table holding the deleted records is a child > > table with an "ON CASCADE DELETE" of one of the tables being updated > > elsewhere in the function. And the function works if called by the owner of > > the table (and the function). > > Further update: I tried changing the owner of the function and table to no > avail. Also tried dropping the FK. It seems to work if called by the > owner of the database, but not otherwise. It still failed after you dropped the FK (I'd have otherwise agreed with Tom that there were some cases that I think were broken as recently as 7.3.x for some value of x on permissions)? How much of the definitions can you let us see? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Auto-update a field when record is changed
Folks, I was wandering if there was a feasible way to automatically update a field in a table, say 'revision_date' in a record whenever any other field in the record is changed. My attempts to use a trigger caused repeating loops and bombed with error. I would like to be able to update such a field automatically anytime a record was updated. Any help would be appreciated. Thanks, Stuart ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Auto-update a field when record is changed
On Fri, 22 Aug 2003, Stuart wrote: > Folks, > > I was wandering if there was a feasible way to automatically update a field > in a table, say 'revision_date' in a record whenever any other field in the > record is changed. My attempts to use a trigger caused repeating loops and > bombed with error. I would like to be able to update such a field > automatically anytime a record was updated. Any help would be appreciated. That seems like a job for a before trigger and changing the to be update/inerted record before the action occurs in general. Don't use an update statement but change the record variables given as the NEW row. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
