[SQL] replace_matches does not return {null}

2011-02-22 Thread Andreas Gaab 
Hi,

I tried to order a text-column only by parts of the entries. Therefore I used 
regexp_matches(), but unfortunately I am loosing rows.

SELECT regexp_matches('abc','[0-9]+'),  regexp_matches('123','[0-9]+');

Does not return "{null}, {123}" but no result at all.

Is this behavior expected? How can I work around?

I am running postgres 8.4


Andreas


___

SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D

Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de

Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___

Re: [SQL] replace_matches does not return {null}

2011-02-22 Thread Tom Lane 
Andreas Gaab  writes:
> I tried to order a text-column only by parts of the entries. Therefore I used 
> regexp_matches(), but unfortunately I am loosing rows.

> SELECT regexp_matches('abc','[0-9]+'),  regexp_matches('123','[0-9]+');

> Does not return "{null}, {123}" but no result at all.

Yes, because regexp_matches returns a rowset of zero or more results.
The fine manual suggests putting it in a sub-select if what you want
is a null or a single result:

  SELECT ... , (SELECT regexp_matches(...)) FROM ...

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Re: Retrieve the column values of a record without knowing the names

2011-02-22 Thread arthur_info 

Thanks Dmitry Grishin. The only problem is that my DBA didn't permit install
this command... :(
But, I quit this problem...
Thanks by attention...
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387932p3396051.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] quotes etc

2011-02-22 Thread John Fabiani 
Hi,
I would have thought that there would be a simple built-in function that would 
escape the quotes as ('D' Andes')  to ('D\' Andes').  But I did not see 
anything?

I am I wrong?

Johnf

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] quotes etc

2011-02-22 Thread Kenneth Marshall 
On Tue, Feb 22, 2011 at 12:26:41PM -0800, John Fabiani wrote:
> Hi,
> I would have thought that there would be a simple built-in function that 
> would 
> escape the quotes as ('D' Andes')  to ('D\' Andes').  But I did not see 
> anything?
> 
> I am I wrong?
> 
> Johnf
> 

The manual goes over many permutations of the escape process and
what options can be used in each case. I would start there, because
for example, escaping a "'" in SQL uses two "'"s: i.e. 'D' Andes'
becomes 'D'' Andes' I think. Procedural languages and libpq can
offer many other options for escape processing.

Cheers,
Ken

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] quotes etc

2011-02-22 Thread Adrian Klaver 
On Tuesday, February 22, 2011 12:26:41 pm John Fabiani wrote:
> Hi,
> I would have thought that there would be a simple built-in function that
> would escape the quotes as ('D' Andes')  to ('D\' Andes').  But I did not
> see anything?
> 
> I am I wrong?
> 
> Johnf

Dollar quoting ? :

http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html
4.1.2.4. Dollar-Quoted String Constants

test(5432)aklaver=>SELECT $$D' Andes$$;
 ?column? 
--
 D' Andes

-- 
Adrian Klaver
adrian.kla...@gmail.com