Re: [SQL] array_agg() with join question

Thank you. I was trying to do the order by at the sub-query level. Thanks again. Woody iGLASS Networks www.iglass.net On Tue, May 14, 2013 at 11:37 AM, Igor Neyman wrote: > > > > On Tue, May 14, 2013 at 10:08 AM, George Woodring < > george.woodr...@iglass.net> wrote:

[SQL] array_agg() with join question

To summarize my question at the top, why is it that when I did the JOIN, the array_agg results reversed order? I had a function that ran the following query: SELECT timeslot, pollgrpid, array_agg(outval) FROM (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot, dsnum) AS foo

Re: [SQL] must appear in GROUP by clause issue

This was helpful, thank you. The issue was that one of the tables had a "name" column added. Changing the alias name in the query so that it was unique was the solution. Thanks for the help, George On Sat, Jun 30, 2012 at 12:34 AM, Lee Hachadoorian < lee.hachadooria...@gmail.com&

[SQL] must appear in GROUP by clause issue

c.long)::text --> max(maptrunc(cpeloc.long))::text however, that one breaks on the first server with the error ERROR: aggregates not allowed in GROUP BY clause LINE 1: select count(*), max(maptrunc(cpeloc.lat, 4.5))::text || max... Any suggestions would be appreciated. George Woodring --

Re: [SQL] Inherited table identification possible

Thanks for all of the help, this was exactly what I was looking for. George On Thu, May 24, 2012 at 2:11 PM, Stephen Belcher wrote: > Yes, the system column "tableoid" identifies the actual table in which the > row is stored. If you cast this to "regclass" you'

[SQL] Inherited table identification possible

if there was a way to do it without the column. Thanks, George -- iGLASS Networks www.iglass.net

Re: [SQL] joining one record according to max value

urce_id, max(score) mxscore from source s > inner join sourcelevel sl on s.source_id = sl.source_id > inner join level l on sl.level_id = l.level_id > group by 1 > ) i > inner join ( > select name, max(score) as mxs from level > group by 1 > ) nm on i.mxscore = nm.mxs > >

Re: [SQL] joining one record according to max value

s, what you like to > have? > > select sl.source_id, max(score) from source s > inner join sourcelevel sl on s.source_id = sl.source_id > inner join level l on sl.level_id = l.level_id > group by 1 > > This is the highest score per source_id. > > 2011/1/12 George Franc

Re: [SQL] joining one record according to max value

nnot be referenced from this part of the query. Character: 601 On Wed, Jan 12, 2011 at 5:35 PM, Jasmin Dizdarevic < jasmin.dizdare...@gmail.com> wrote: > Because of the "score desc limit 1". The subselect returns only the higest > score and this is level 2. > > 2011/1

[SQL] joining one record according to max value

level.level_id = level.level_id order by score desc limit 1 ) as temp on temp.source_id = source.source_id; -- George

[SQL] Advice returning data as an array

I have the following query that works the way I need: SELECT '20090812' AS date, sum(in_oct) AS bytes, sum(in_pkt) AS pkts, 'appl' AS name, ARRAY['appl'] AS fields, CAST((LEAST(src_port, dst_port)) AS varchar) as fieldVal FROM flow_history.flows_20090812 GROUP BY fieldVal ORDER BY bytes DESC LIMIT

Re: [SQL] Object create date

1. not exactly what you were looking for, but i answer this partially by putting a commented-out CVS expansion tag (e.g. $Id:) in the body of the function so that it gets into the catalog and can be searched: CREATE OR REPLACE FUNCTION foo () RETURNS void AS $BODY$ -- $Id: foo.sql,v 1.6

Re: [SQL] create table with rownames as values in column of seciond table

your problem is a little unorthodox, but i will spare you the "why the heck do you want to do this?" discussion and assume you have good reasons... so here's a "dynamic SQL" approach: select 'create table test (id bigint, '|| array_to_string(array(select a||' text' from foo),', ')||');'; not pret

Re: [SQL] finding unused indexes?

> From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 10, 2007 6:36 AM > > "George Pavlov" <[EMAIL PROTECTED]> writes: > > so is it safe to say that an index that has > > pg_stat_user_indexes.idx_scan, &g

Re: [SQL] finding unused indexes?

pg_statio_user_indexes.idx_blks_read > 0 and most of those have pg_statio_user_indexes.idx_blks_hit > 0. when/why would that happen? i guess i don't entirely understand those two values so an explanation would be very welcome (maybe an example of when each of the five values gets incremented. than

[SQL] minimum bounding circle for a triangle/polygon

generalization for any polygon, but that's probably too much. thanks in advance! george

Re: [SQL] ERROR: column "crc" does not exist

rom base.points where "CRC" = 'e19e26330a0db2f2435106b16623fa82'; Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] dropping a schema and cross-schema dependencies

So, for example we might have a notice like this: NOTICE: table test.employee column first_name depends on type name_ud So, if the word "table" is followed by a word that contains a period I have a cross-schema dependency. This could work. I just want to be comprehensive. George --

[SQL] dropping a schema and cross-schema dependencies

ALOG that can be used to check for any cross-schema dependencies and halt before issuing a DROP with a CASCADE? TIA, George ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [SQL] Monitor what command is executing at the backend

> How will I enable command string to see the commands? in your postgresql.conf set stats_command_string = true read http://www.postgresql.org/docs/8.2/interactive/monitoring-stats.html for details ---(end of broadcast)--- TIP 3: Have you checked

Re: [SQL] Monitor what command is executing at the backend

>Is there a way to see from the log files on what sql statement is > currently by which user? In other words, I want to monitor > the DB activity. for a current snapshot you don't need the logs, try: select * from pg_stat_activity; (command string needs to be enabled for your database.)

Re: [SQL] huge disparities in =/IN/BETWEEN performance

_id) where user_id = 41; This does improve things a lot, bringing the execution time for this particular user to 3ms (!), but it is quite ugly and not fast enough for me for a user_id with lots of associated stuff_ids. George ---(end of broadcast)--

Re: [SQL] huge disparities in =/IN/BETWEEN performance

dea that this happens? is this a better statement of the issue? thanks for listening! george ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

[SQL] huge disparities in =/IN/BETWEEN performance

with GROUP BYs on user_id and various subqueries, but my basic thought is that should not really matter... i am on 8.1.3. i'd like to hope that the 8.2 optimizer improvements might help with this but i haven't tested. george ---(end of broadcast)--

Re: [SQL] null values in non-nullable column

strikes me as too drastic and as having the potential of breaking existing functionality. I am sure I am missing something, just my two cents... George ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at

[SQL] null values in non-nullable column

ERROR: domain boolean_not_null does not allow null values I would have expected failure at the table creation step, but it proceeds (and inserts the nulls). Interestingly, I do see a failure after I try to restore the table from a dump (using pg_dump/pg_restore). George ---(e

Re: [SQL] converting Informix outer to Postgres

Why don't you start by presenting the query in a more readable form (hint use SQL-standard JOIN syntax for all of your joins) and maybe narrowing just to a couple of tables to isolate the problem. As it is, it is hard to understand. In the process of rewriting you will be forced to think about each

Re: [SQL] a celko-puzzle from long time ago

am not sure what DBMS lets Mr. Celko insert the string 'Orange' into a CHAR(5) field, but he's a smartie, so I am sure there must be a way ;-) George > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Richard > Broersma

Re: [SQL] record datatype comparisons

t type(s). You may need to add explicit type casts. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan > Sent: Tuesday, October 31, 2006 1:23 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] record datatype comparison

[SQL] record datatype comparisons

(1::int,'a'::varchar) is distinct from (select (2::int,'a'::varchar))); --> throws the error Both (1::int,'a'::varchar) and (select (2::int,'a'::varchar)) are of type record, aren't they? In real life I want the right side of the IS DI

Re: [SQL] How to query information schema from shell script

simplest might be psql -l. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jon Horsman > Sent: Friday, October 27, 2006 8:13 AM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to query information schema from shell script > > In my original

Re: [SQL] How to query information schema from shell script

db0 -tA -c"select table_name from information_schema.tables where table_name like 'employee%'"`; do for> echo FOUND TABLE $tn for> done FOUND TABLE employee_role FOUND TABLE employee hope this helps, george ---(end of broadcast)

Re: [SQL] optimal insert

And don't forget that \COPY and especially COPY are usually much faster (and, IMHO, easier to compose/maintain) than gobs of INSERTs. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono > Sent: Tuesday, October 10, 2006 1:46 PM > To: Dirk Ja

Re: [SQL] viewing the description of tables from python DB-API

On Tue, 1 Aug 2006 15:30:48 -0700 "Daniel Joo" <[EMAIL PROTECTED]> wrote: > Is there a way to view the list of all tables from python (or any other > languages for that matter) DB-API? What I'm looking for is a command > similar to the meta-command '\d' that works with the psql client. The cur

[SQL] finding unused indexes?

if there is a slicker/less cumbersome way of doing it. Also indexes used by functions are hard to simulate that way. George ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Triggers using PL/pgSQL

ng Audit Changes with Composite Typed Columns). It provides a very concise way of tracking all changes, possible qualifying as a "best approach"? Regards, George ---(end of broadcast)--- TIP 4: Have you searched our lis

Re: [SQL] Error when trying to use a FOR loop

orating it into a PL/pgSQL function? See http://www.postgresql.org/docs/8.1/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Regards, George ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: Fwd: [SQL] Start up question about triggers

lumns). Regards, George - Original Message - From: "Forums @ Existanze" <[EMAIL PROTECTED]> To: Sent: Friday, June 23, 2006 2:15 AM Subject: Re: Fwd: [SQL] Start up question about triggers Hello again aaron, Im really interested in the part where you say "generi

Re: [SQL] Problems Testing User-Defined Function

ECTED]', '[EMAIL PROTECTED]', 'test.txt', NULL, '2006/06/09') Is this a direct paste? If so, you are missing the apostrophe before me@ you.com. Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Query to return modified results at runtime?

Richard Broersma Jr wrote: IDColor --- --- 1 Blue 2 Red 3 Green 4 Orange How would I rewrite the query to return results where the colors are replaced by letters to give the following results? IDColor --- --- 1 A 2 D 3 B 4 C http://www.po

[SQL] Concat two fields into one at runtime

name --- --- 1 George Handin 2 Joe Rachin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[SQL] Query to return modified results at runtime?

I have a query: SELECT * FROM testtable; Where the results are: IDColor --- --- 1 Blue 2 Red 3 Green 4 Orange How would I rewrite the query to return results where the colors are replaced by letters to give the following results? IDColor --- --- 1 A 2

Re: [SQL] find all tables with a specific column name?

e/infoschema-columns.html -- George Young On Fri, 21 Apr 2006 09:29:33 -0700 (PDT) Jeff Frost <[EMAIL PROTECTED]> wrote: > Is there a reasonable way to extract a list of all tables which contain a > specific column name from the system views on 8.1? > > For instance, I might

[SQL] trigger to enforce FK with nulls?

rmace should be a problem. No updates are ever made to steps.opset and steps.step, or to opset_steps.(opset,step) [though updates are often made to *other* fields of steps]. -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?&qu

Re: [SQL] Expressing a result set as an array (and vice versa)?

On Thu, 23 Mar 2006 11:44:32 -0800 Don Maier <[EMAIL PROTECTED]> threw this fish to the penguins: > Is it possible to construct an array from an appropriate select > expression that generates a result set of unknown cardinality? > To focus on the simple case: Is it possible to construct a one-

Re: [SQL] Custom type

le | postgres | public | hold_log | table | postgres | ... But not if you specify the object: newschm3=# \d+ fffg Table "public.fffg" Column | Type | Modifiers | Description +-+---+- t | text|

Re: [SQL] Copying a row within table

*shares* the sequence. insert into foo select * from foo_tmp; drop table foo_tmp; If there's any chance of concurrent update/insert/deletes to foo, you might should wrap this in a (begin; stuff; commit) transaction. -- George Young On Tue, 14 Mar 2006 09:19:49 +0200 Aarni Ruuhimäki <[EM

Re: [SQL] Change date format through an environmental variable?

Try the PGDATESTYLE environment variable. Works in 7.4 and 8.1, though it is claimed to be deprecated. -- George Young On Wed, 01 Mar 2006 12:32:26 -0500 Mark Fenbers <[EMAIL PROTECTED]> threw this fish to the penguins: > I want to get Pg (v7.4.7) to output a date field in a differe

Re: [SQL] CREATE VIEW form stored in database?

- Original Message - From: "Mario Splivalo" <[EMAIL PROTECTED]> If you keep your definition in a script file, you can copy the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. It's still a pain. If I have two dozen vie

Re: [SQL] unique constraint instead of primary key? what

nce more than one row". If the above query returns 10 rows, what's wrong with that? Nothing *requires* a PRIMARY KEY at all, anyway. -- puzzled... George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] unique constraint instead of primary key? what

On Wed, 08 Feb 2006 18:34:22 -0800 Ken Hill <[EMAIL PROTECTED]> threw this fish to the penguins: > On Wed, 2006-02-08 at 21:04 -0500, george young wrote: > > > [PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] > > I'm designing a completely n

[SQL] unique constraint instead of primary key? what disadvantage(ODBC usage)?

ely with the database? Will the planner produce lousy query plans? Will Edgar Codd haunt my dreams? -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] trecherous subselect needs warning?

On Tue, 07 Feb 2006 12:45:53 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > > This query returns zero rows: > > > newschm3=# select run_id from s_bake where opset_id not in (select opset_id > > from opset_steps); &

[SQL] trecherous subselect needs warning?

has any rows! Eeek! I suppose the moral of the story is to ALWAYS, absolutely ALWAYS qualify a correlation name (table alias). Of course, what I meant in the original query was: select s.run_id from s_bake s where s.opset_id not in (select os.opset_id from old_opset_steps os); Sigh. Am I miss

[SQL] strange quoted csv behavior with COPY

from foo; length | a| b ++--- 6 | bar| 3 is this by design? what is the use of the quote in this context? i am on 8.0.6. george ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your de

Re: [SQL] CREATE VIEW form stored in database?

y the script and paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then execute the script from there. Regards, George ---(end of broadcast)--- TIP 6: explain analyze is your friend

[SQL] non-equi self-join optimization

on position (t1.myname in t2.myname) > 0 where t1.flag = 1 and t2.flag = 2 ; I have gone through a few variations on the theme, but none perform too well. Any advice on the best way to optimize a query like this would be appreciated. Thanks! George ---(end

Re: [SQL] PostgreSQL and uuid/guid

r written a C postgreSQL function, and any help (or > documentation pointout) would be greatly appreciated. > If I posted this to the wrong mailing list, please point me out to the > correct one. You question is quite welcome here! -- George Young -- "Are the gods not just?"

[SQL] avg() with floating-point types

I have city and postal_code tables linked by city_postal_code through a city_id and postal_code_id. The postal_codes have latitude/longitude, the cities don't. I want to set the city lat/long to the average of the associated postal codes (abstract for a minute on whether that actually makes sense f

Re: [SQL] DISTINCT with NULLs and INT fields

> > test=# insert into foo values (4::int,4::int); > > INSERT 0 1 > > test=# insert into foo values (4::text,4::text); > > ERROR: column "b" is of type integer but expression is of type text > > HINT: You will need to rewrite or cast the expression. > > test=# insert into foo values (cast(4 as

Re: [SQL] Help with simple query

or, from the "stupid tricks" category: SELECT n.user_id, max(lpad(extract(epoch from n.modified_date),12,'0') || ' ' || note) FROM notes n GROUP by n.user_id i am not *really* suggesting this! ---(end of broadcast)--- TIP 5: don't forget

Re: [SQL] DISTINCT with NULLs and INT fields

> Sure, but in this example the required type of the value is clear from > immediate context (ie, the INSERT). This is one of the cases where > the SQL spec requires a bare NULL to be accepted. (BTW, 'no' and '4' > in this example are *not* values of type text; they are > untyped literals which

Re: [SQL] DISTINCT with NULLs and INT fields

> "Better" is in the eye of the beholder. sorry for the value-laden term. "laxer" is more appropriate, of course! the funny thing is that had they cast the NULLs to TEXT it would have failed there too (they do not do implicit TEXT to INT). > It surprises me not at all that > Microsoft would be

[SQL] DISTINCT with NULLs and INT fields

conversion from INT. Any thoughts on what the "correct" behavior should be here? George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] "large" IN/NOT IN subqueries result in query returning wrong data

null) --> 0, no matter what the goo values are Sorry... Thanks! George ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

[SQL] "large" IN/NOT IN subqueries result in query returning wrong data

The following looks like a bug to me, but please someone review and tell me what I might be missing. Seems that past a certain result set size a "[NOT] IN (subquery)" stops behaving as expected and returns 0 matches even when there should be matches. No errors are returned, just faulty data. The ex

Re: [SQL] The Information Schema vs the PG Catalog

haven't seen them represented anywhere there (please someone correct me if I am wrong). You can get more information about sequences from pg_catalog.pg_class (look for pg_class.relkind='S') and various views that sit on top of that (e.g. pg_statio_all_sequences). George > I'm wr

Re: [SQL] how to convert relational column to array?

On Mon, 19 Dec 2005 09:54:49 -0700 Michael Fuhr <[EMAIL PROTECTED]> threw this fish to the penguins: > On Mon, Dec 19, 2005 at 11:06:12AM -0500, george young wrote: > > create table new_tab(name text, id int, permits text[]); > > > > -- I insert one row per name: >

[SQL] how to convert relational column to array?

81 | {operator} lawless | 509 | {operator,originator} lcalvet | 622 | {originator} loomis | 514 | {operator,originator} pig | 614 | {operator,originator,supervisor} -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (

[SQL] rename idx's with table; avoid confusing idx names?

;old_foo')? Does this look useful enough for me to package more formally? -- George Young CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name text) returns void AS $$ declare

Re: [SQL] problems with array

(pseudo function? builtin? whatever); no subquery is needed: select c.* from tb_cat c,tb_array a where a.id=1 and c.id=any(a.cat); Look at section 8.10.5 "Searching in Arrays" in http://www.postgresql.org/docs/7.4/interactive/arrays.html and section 9.17.3 in: http://www.postgre

Re: [SQL] tid_le comparison for tuple id (ctid) values?

On Mon, 21 Nov 2005 16:19:28 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > >update steps set x=x||'X' from steps s where steps.key1=s.key1 and > > steps.key2=s.key2 and step.ctid > > But this fails becau

[SQL] tid_le comparison for tuple id (ctid) values?

[PostgreSQL 8.1.0 on i686-pc-linux-gnu] I would like to suggest that there be a less-than (or greater-than) operator for the 'tid' type. I used to use oid's for finding and distinguishing duplicate data. Now that oid's are not included by default (and I do not quarrel with that change), I though

[SQL] how to update table to make dup values distinct

can add multiple colons, or one each of an assortment of characters, say ':+*&^#'. Performance does not matter here. The real table has 30K rows, ~200 dups. To clarify, I want to end up with something like: 1 | the 2 | the: 3 | rain 4 | in 5 | mainly: 6 | spain 7 | stays

Re: [SQL] Design question: Scalability and tens of thousands of

e table values(owner text, obj text, name text, val text) That is, the values are stored in text type, not the native type. Yes, this takes a performance hit for conversion of values, but the simplicity of schema really wins for me. I suggest you seriously consider it unless you need blinding perfo

[SQL] 8.0.x windows installer fails?

ts adn that you can access it or contact the appilcation vendor to verify that this is a valid Windows Installer package. So I tried the analgous file from 8.0.3, with the same results. What am I doing wrong? -- George Young -- "Are the gods not just?" "Oh no, child. What would become

Re: [SQL] owner of data type "areas" appears to be invalid ?

Yes, that worked. Thank you very much! -- George On Fri, 14 Oct 2005 12:04:13 -0400 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young writes: > > How can I fix this? > > Re-create the owning user (which you evidently dropped), assigning it >

[SQL] owner of data type "areas" appears to be invalid ?

with usesysid=501. There is no row in pg_user with usesysid=101, and there is none with usename "101". How can I fix this? I must be able to get clean dumps that can be reloaded in case of a crash. -- George Young pig5=> select * from pg_class where relname='areas&#

Re: [SQL] SYNTAX ERROR ON FOR... LOOP

Hi Rodrigo, - Original Message - From: "Rodrigo Carvalhaes" <[EMAIL PROTECTED]> To: Sent: Wednesday, April 27, 2005 12:39 PM Subject: [SQL] SYNTAX ERROR ON FOR... LOOP Hi Guys, I am having a "simple syntax problem" but very strange... I am trying to make an IF / ELSE / END IF inside of

Re: [SQL] Query performance problem

7;b',4); INSERT 98686 1 jan28-05=# insert into test values('c',NULL); INSERT 98687 1 jan28-05=# insert into test values('d'); INSERT 98688 1 jan28-05=# select * from test; foo | foo1 -+-- a | 1 b |4 c | d |0 (4 rows) George - Original Message

Re: [SQL] Generic Function

- Original Message - From: <[EMAIL PROTECTED]> To: Sent: Monday, March 14, 2005 12:15 PM Subject: [SQL] Generic Function Hi, Can I built a generic function like: CREATE FUNCTION f (text) RETURNS TEXT as $$ return 'select * from $1'; $$ I know its impossible as writed. Also I have lo

Re: [SQL] Working with XML.

Title: Message Hi Theo,   Hmm.  Well we're getting into territory that's over my head.  I've simply been a user of xml2 and do not know much about its inner workings.  Just out of curiosity, what is the size of Sort_Mem in your postgresql.conf?   Regards, George - O

Re: [SQL] Working with XML.

worked as expected.   Perhaps the client you're using is causing the problem.  Can you run the query in pgAdmin?   Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Cc: 'pgsql-sql@postgresql.org' Sent: Monday, Febr

Re: [SQL] Working with XML.

),  errmsg("xpath_table must be called as a table function")));should be changed to:     elog(ERROR,"xpath_table must be called as a table function"); I have attached an edited copy of xpath.c with these changes, if you would like to work with it.   Regards, George--

Re: [SQL] Working with XML.

perly - I should be able to help you with that.  I don't think you can use it on any version earlier than 7.2.   Regards, George - Original Message - From: Theo Galanakis To: 'George Weaver' Sent: Tuesday, February 15, 2005 4:30 PM Subject: RE: [SQL] Workin

Re: [SQL] Working with XML.

:13:18''}2d4mat2Lptmdydtgqnc5vnuzxgqxiykqn1c4pr0oduk5gwz25b3Lq05dzqxetee9om45b69ytb48uvziL5xrvyu4k7ng4oouihx0764929641215{ts ''2005-02-14 16:13:18''}1z5x6dLc9n1vtcLbmk7q6hdriqLa2ub4et41podr7izcro4nkkLsvp9p0s7obzr27txvknn92m0ps4riqqx7puqoLn2wpmm2z50r'jan28-05(# ,&

Re: [SQL] Working with XML.

  Microsoft has a very good reference on xpath expressions: http://msdn.microsoft.com/library/default.asp?url="">.   Regards, George - Original Message - From: Theo Galanakis To: pgsql-sql@postgresql.org Sent: Sunday, February 13, 2005 11:48 PM Subject: [SQL]

Re: [SQL] PL/PgSQL - returning multiple columns ...

. You would then return r, comprised of r.server_name and r.load_avg. George - Original Message - From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 02, 2005 3:10 PM Subject: [SQL] PL/PgSQL - returning multiple columns ... I have a function that I

Re: [SQL] Moving from Transact SQL to PL/pgsql

an openacs 5. George Essig ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Returning A Varchar From A Function

Hi Richard, What happens if you just do: where trim(status) = trim($1) Regards, George - Original Message - From: "Richard Hurst" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 12, 2004 4:04 AM Subject: [SQL] Returning A Varchar From A Func

Re: [SQL] Question about a CIDR based query

sh Berkus) wrote in message news:<[EMAIL PROTECTED]>... > George, > > > Um, no, I need an EXPLAIN ANALYZE, not just an EXPLAIN. Thanks. > > > > > June_03=# explain select * from tmp where ro

Re: [SQL] Question about a CIDR based query

I did a vacuum analyze before I run the following explain June_03=# explain select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN Seq Scan on tmp (cost=0.00..606.60 rows=1

Re: [SQL] Rank

rank from mytable t1,mytable t2 where t2.point >=t1.point group by t1.id,t1.site_name,t1.point; id | site_name | point | rank +---+---+-- 3 | Site D|22 |4 2 | Site B|90 |2 4 | Site X|98 |1 1 | Site A|40 |3 (4 rows) -- G

Re: [SQL] Syntax for cmd to EXEC...how many quotes?

#x27;, count(*) FROM \' || r_rec.viewname || \' ; \'; HTH George - Original Message - From: "David B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 20, 2004 6:24 PM Subject: [SQL] Syntax for cmd to EXEC..

Re: [SQL] debugging query to put message in pg logfile?

On Thu, 04 Mar 2004 16:35:01 -0500 Tom Lane <[EMAIL PROTECTED]> threw this fish to the penguins: > george young <[EMAIL PROTECTED]> writes: > > I've started putting debugging queries like: > >select "opwin.py: committing step signoff" > >

[SQL] debugging query to put message in pg logfile?

e of code did a 'commit', since there's nothing to distinguish one from another in the log. Is there some cheaper (or more appropriate) sql statement that will show up in the postgres log? I thought I remembered a "message" sql statement or something like that. -- George You

[SQL] Enterprice support in PostgreSQL

Hi all,   We are providing database solutions in postgreSQL...Now using PostgreSQL 7.3. It is performing well. But Now we have some enterprice level requirements. One of Our requirement is to provide a distributed solution in PostgreSQL. The questions are...1. Is it posible to provide a distributed

[SQL] Distributed Transactions

Hi all,   i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. is there a transaction coordinator available for Postgres..   thanks in advance regards jinujose Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.

[SQL] Infinite loop crashes server

hi all, i am using PostgreSQL 7.3.2 on redhat linux 9. there is problem when executing pl/pg sql functions. if the function enter an infinite loop. the server is hanged. cannot cancel the query. the linux itself is hanged.i cannot kill postgres process. i have to reboot the machine manually.. waite

Re: [SQL] increment int value in subset of rows?

thing O(0), i.e. a few queries regardless of the number of rows... > - Original Message - > From: george young > To: [EMAIL PROTECTED] > Sent: Monday, November 24, 2003 1:59 AM > Subject: [SQL] increment int value in subset of rows? > > > [postgresq

  1   2   >