SQL discussion

gherzig | 1 Feb 2009 02:13

Re: dynamic OUT parameters?

> gherzig <at> fmed.uba.ar wrote:
>
>> Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD
>> forces the use of OUT parameters. I will give your idea a try.
>
> Tom Lane's point about using a refcursor is (unsurprisingly) a good one.
> If you return a refcursor from your function, you don't have to do any
> special work to call the function, and you can (with most DB access
> APIs) FETCH records from the cursor rather conveniently.
>
> See:
>
> http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
Well, aparenty my problem remains, because the boss want that the
programmers just need to call
select * from report('table_name'). Following your previous sample
function, im forced to use it in the form
select * from report('table_name') as x(a int, b varchar, c int), and that
"as x(...)" is the kind of thing hes triyng to avoid. Same feeling about
fetching records at application level. To bad for me, im affraid :(

Gerardo

(Continue reading)

headers

Craig Ringer | 1 Feb 2009 04:42

Re: dynamic OUT parameters?

gherzig <at> fmed.uba.ar wrote:

> Well, aparenty my problem remains, because the boss want that the
> programmers just need to call
> select * from report('table_name').

Then, AFAIK, you're not going to have much luck, as Pg needs to know the
columns that'll be output before the function is called. Applications
and DB access interfaces also usually also need to know the column list
beforehand.

If you return a refcursor you can at least use:

SELECT report('table_name');
FETCH ALL IN 'cursorname';

The documentation has a more full example:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465

Personally, I find it difficult to imagine what could be wrong with that.

--
Craig Ringer

--

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

(Continue reading)

headers

Ivan Sergio Borgonovo | 1 Feb 2009 10:54

Re: dynamic OUT parameters?

On Sun, 01 Feb 2009 12:42:12 +0900
Craig Ringer <craig <at> postnewspapers.com.au> wrote:

> gherzig <at> fmed.uba.ar wrote:
> 
> > Well, aparenty my problem remains, because the boss want that the
> > programmers just need to call
> > select * from report('table_name').
> 
> Then, AFAIK, you're not going to have much luck, as Pg needs to
> know the columns that'll be output before the function is called.
> Applications and DB access interfaces also usually also need to
> know the column list beforehand.
> 
> If you return a refcursor you can at least use:
> 
> SELECT report('table_name');
> FETCH ALL IN 'cursorname';
> 
> The documentation has a more full example:
> 
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465
> 
> Personally, I find it difficult to imagine what could be wrong
> with that.

I've followed this thread with interest.
I'm starting to manage larger and larger code base of plpsql.
What I find hard to achieve is code reuse and implication of some
techniques to performances.

(Continue reading)

headers

Pavel Stehule | 1 Feb 2009 12:31

Re: dynamic OUT parameters?

Hello

try to look on

http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html

regards
Pavel Stehule

2009/2/1 Ivan Sergio Borgonovo <mail <at> webthatworks.it>:
> On Sun, 01 Feb 2009 12:42:12 +0900
> Craig Ringer <craig <at> postnewspapers.com.au> wrote:
>
>> gherzig <at> fmed.uba.ar wrote:
>>
>> > Well, aparenty my problem remains, because the boss want that the
>> > programmers just need to call
>> > select * from report('table_name').
>>
>> Then, AFAIK, you're not going to have much luck, as Pg needs to
>> know the columns that'll be output before the function is called.
>> Applications and DB access interfaces also usually also need to
>> know the column list beforehand.
>>
>> If you return a refcursor you can at least use:
>>
>> SELECT report('table_name');
>> FETCH ALL IN 'cursorname';
>>
>> The documentation has a more full example:

(Continue reading)

headers

Bart Degryse | 2 Feb 2009 09:38

Re: regexp_replace and UTF8

Thanks for the ideas!

The function Jasen suggests works partially.

There are also entities like

‘

’

“

”

…

The output of the htmlent function for these looks like |

Was that what you meant with "characters outside of the LATIN-1 space are not handled

but apparently this is what you want." ?

Because in that case, they should be handled too.

How should that be done.

Thanks,

Bart

>>> Jasen Betts <jasen <at> xnet.co.nz> 2009-01-31 12:47 >>>
On 2009-01-30, Bart Degryse <Bart.Degryse <at> indicator.be> wrote:
>
> --=__Part8EA648F8.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Hi,
> I have a text field with data like this: 'de patiënt niet'
> (without the quotes).
> I would like to convert this string to look like this: 'de patiënt
> niet'
> Basically what I need to do (I think) is
> - get rid of the &, # and ;
> - convert the number to hex
> - make a UTF8 from that (thus: \xEB)
that is not UTF8.

the UTF8 representation for 'ë' in SQL is e'\xC3\xAB' or chr(235)

your input appears to be encoded in LATIN-1 (or possibly 8859-13)

I think you'll need to write a function.

here, where I have database encoding UTF8 this appears to work as you
desire.

CREATE or replace FUNCTION htmlent(inp text) returns text as
$f$
DECLARE
str text;
BEGIN
str= regexp_replace(quote_literal( inp)
                    ,$$&#(\d+);$$
    ,$$'||chr(\1)||'$$
    ,'g');
execute 'select '||str into str;
return str;
END
$f$
LANGUAGE PLPGSQL;

select htmlent('de patiënt niet');

probably the above should be expanded to handle named entities
like '&' too.

characters outside of the LATIN-1 space are not handled
but aparently this is what you want.

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

headers

Nikhil teltia | 3 Feb 2009 01:06

Function Returning a Set of Composite Value

Hi All ,
I have a function f_wrapper(seq,pat) which returns a composite value of type (int,int[][]);
I am aware that I can run a query somthing like this to get it working

select * from f_wrapper('XYZ,'X') as m1(mid int,match int[][]);

but I want to pass another table column as parameter to function. - select f_wrapper( t.seq,'X') from t_sequence t;
this return result which looks like -

(0,"{{0,2,2}}")
(1,"{{10,2,2}}")

when I try to parse this result in following way -
select f_wrappwe(t.seq,'X') frm t_sequence t as m1(mid int,match int[][]) ; I get following syntax error

ERROR: syntax error at or near "int"
LINE 1: ...f_wrapper(seq,'MK') from nik_small_file as m1(mid int,match ...
^
any idea why I am getting this error or what is the alternative to parse composite value in select column ?

my purpose is to to show some column from table+(result from f_wrapper function) but I am not able to parse f_wrapper result in required column without calling f_wrapper twice like this -
select (f_wrapper(seq,'MK')).mid , (f_wrapper(seq,'MK')).match from nik_small_file;

since my f_wrapper is going to be a heavy function so I can't afford to call it twice.
any suggestion regarding this will be helpful.

Regards,
Nikhil

PS: Earlier I have sent same question to pgsql-novice as well but then I realised that this might be more relevant on this list. sorry for posting it twice.

Check out the all-new Messenger 9.0! Click here.

headers

Jasen Betts | 4 Feb 2009 10:59

Re: regexp_replace and UTF8

On 2009-02-02, Bart Degryse <Bart.Degryse <at> indicator.be> wrote:
>
> --=__PartF6DE34E1.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> Thanks for the ideas!
> The function Jasen suggests works partially.
> There are also entities like
> &#8216;
> &#8217;
> &#8220;
> &#8221;
> &#8230;

these work fine here.

jasen=# select htmlent('&#8216; &#8217; &#8220; &#8221; &#8230;');
  htmlent  
  -----------
   ‘ ’ “ ” …
   (1 row)

> The output of the htmlent function for these looks like |

I see a pipe symbol '|' is that what you wrote?

> Was that what you meant with 
>>"characters outside of the LATIN-1 space
>> are not handled but apparently this is what you want." ?

I was under a mistaken impression of the encoding of HTML numeric
entities.

> Because in that case, they should be handled too.
> How should that be done.
> Thanks,

it works here because server_encoding is UTF8;

to check do this.

  show server_encoding;show client_encoding;

it looks like you's need to convert the numbers to utt-8 in a bytea
type and then use convert to translate then to your preferred encoding
(appears to be win125x)

postgresql8.3 appears to provide no way to generate UTF-8 in a bytea
type so if you can't use a utf-8 encoding for your database you'll
have to write your own.

>>>> Jasen Betts <jasen <at> xnet.co.nz> 2009-01-31 12:47 >>>
> On 2009-01-30, Bart Degryse <Bart.Degryse <at> indicator.be> wrote:
>>
>> --=3D__Part8EA648F8.0__=3D
>> Content-Type: text/plain; charset=3DUTF-8
>> Content-Transfer-Encoding: quoted-printable
>>
>> Hi,
>> I have a text field with data like this: 'de pati&#235;nt niet'
>> (without the quotes).
>> I would like to convert this string to look like this: 'de pati=C3=ABnt
>> niet'
>> Basically what I need to do (I think) is
>> - get rid of the &, # and ;
>> - convert the number to hex
>> - make a UTF8 from that (thus: \xEB)
> that is not UTF8.
>
> the UTF8 representation for '=C3=AB' in  SQL is e'\xC3\xAB' or chr(235)
>
> your input appears to be encoded in LATIN-1 (or possibly 8859-13)
>
>
> I think you'll need to write a function.
>
> here, where I have database encoding UTF8 this appears to work as you
> desire.
>
> CREATE or replace FUNCTION htmlent(inp text) returns text as
> $f$
> DECLARE
>   str text;
> BEGIN
> str=3D regexp_replace(quote_literal( inp)
>                     ,$$&#(\d+);$$
>     ,$$'||chr(\1)||'$$=20
>     ,'g');
> execute 'select '||str into str;
> return str;
> END
> $f$
> LANGUAGE PLPGSQL;
>
> select htmlent('de pati&#235;nt niet');
>
> probably the above should be expanded to handle named entities=20
> like '&amp;' too.
>
> characters outside of the LATIN-1 space are not handled=20
> but aparently this is what you want.
>
>
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
> --=__PartF6DE34E1.0__=
> Content-Type: text/html; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
><HTML><HEAD>
><META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
><META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
><BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
><DIV>Thanks for the ideas!</DIV>
><DIV>The function Jasen suggests works partially.</DIV>
><DIV>There are also entities like</DIV>
><DIV>&amp;#8216;</DIV>
><DIV>
><DIV>&amp;#8217;</DIV>
><DIV>&amp;#8220;</DIV>
><DIV>&amp;#8221;</DIV>
><DIV>&amp;#8230;</DIV>
><DIV>The output of the htmlent function&nbsp;for these looks like | </DIV>
><DIV>Was that what you meant with "characters outside of the LATIN-1 space =
> are not handled</DIV>
><DIV>but apparently this is what you want."&nbsp;?</DIV>
><DIV>Because in that case, they should be handled too.</DIV>
><DIV>How should that be done.</DIV>
><DIV>Thanks,</DIV>
><DIV>Bart&nbsp;</DIV><BR><BR>&gt;&gt;&gt; Jasen Betts &lt;jasen <at> xnet.co.nz&=
> gt; 2009-01-31 12:47 &gt;&gt;&gt;<BR>On 2009-01-30, Bart Degryse &lt;Bart.D=
> egryse <at> indicator.be&gt; wrote:<BR>&gt;<BR>&gt; --=3D__Part8EA648F8.0__=3D<B=
> R>&gt; Content-Type: text/plain; charset=3DUTF-8<BR>&gt; Content-Transfer-E=
> ncoding: quoted-printable<BR>&gt;<BR>&gt; Hi,<BR>&gt; I have a text field =
> with data like this: 'de pati&amp;#235;nt niet'<BR>&gt; (without the =
> quotes).<BR>&gt; I would like to convert this string to look like this: =
> 'de pati=C3=ABnt<BR>&gt; niet'<BR>&gt; Basically what I need to do (I =
> think) is<BR>&gt; - get rid of the &amp;, # and ;<BR>&gt; - convert the =
> number to hex<BR>&gt; - make a UTF8 from that (thus: \xEB)<BR>that is not =
> UTF8.<BR><BR>the UTF8 representation for '=C3=AB' in&nbsp; SQL is =
> e'\xC3\xAB' or chr(235)<BR><BR>your input appears to be encoded in LATIN-1 =
> (or possibly 8859-13)<BR><BR><BR>I think you'll need to write a function.<B=
> R><BR>here, where I have database encoding UTF8 this appears to work as =
> you<BR>desire.<BR><BR>CREATE or replace FUNCTION htmlent(inp text) returns =
> text as<BR>$f$<BR>DECLARE<BR>&nbsp; str text;<BR>BEGIN<BR>str=3D regexp_rep=
> lace(quote_literal( inp)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
> ;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; =
> ,$$&amp;#(\d+);$$<BR>&nbsp;&nbsp;&nbsp; ,$$'||chr(\1)||'$$ <BR>&nbsp;&nbsp;=
> &nbsp; ,'g');<BR>execute 'select '||str into str;<BR>return str;<BR>END<BR>=
> $f$<BR>LANGUAGE PLPGSQL;<BR><BR>select htmlent('de pati&amp;#235;nt =
> niet');<BR><BR>probably the above should be expanded to handle named =
> entities <BR>like '&amp;amp;' too.<BR><BR>characters outside of the =
> LATIN-1 space are not handled <BR>but aparently this is what you want.<BR><=
> BR><BR>-- <BR>Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)<BR=
>>To make changes to your subscription:<BR><A href=3D"http://www.postgresql.=
> org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</A><BR=
>></DIV></BODY></HTML>
>
> --=__PartF6DE34E1.0__=--
>

--

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

headers

F. | 4 Feb 2009 22:57

postgre2postgre


Hello,
I am trying to migrate from postgresql-8.1.11-1.el5_1.1 (i386) to
postgresql-8.3.4-1.fc10.x86_64. But I can not.

Database uses ltree and tsearch and the problem seems to be this.

I am using,
pg_dump in first computer and psql in second computer to execute script.

First error:
psql:informatica.sql:24: ERROR:  no se encuentra la función «gtsvector_in» en el archivo «/usr/lib64/pgsql/tsearch2.so»

Anyone know any way to migrate?

-- 

--
Publicidad http://www.pas-world.com

--

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

headers

Devrim GÜNDÜZ | 4 Feb 2009 23:41

Re: postgre2postgre

On Wed, 2009-02-04 at 22:57 +0100, F. wrote:
> First error:
> psql:informatica.sql:24: ERROR:  no se encuentra la función
> «gtsvector_in» en el archivo «/usr/lib64/pgsql/tsearch2.so»
> 
> Anyone know any way to migrate?

Tsearch2 was integrated in core as of 8.3. You will need to read this:

http://www.postgresql.org/docs/8.3/static/textsearch-migration.html

Regards,
--

-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

headers

Chris | 4 Feb 2009 23:42

Re: postgre2postgre

F. wrote:
> Hello,
> I am trying to migrate from postgresql-8.1.11-1.el5_1.1 (i386) to
> postgresql-8.3.4-1.fc10.x86_64. But I can not.
> 
> Database uses ltree and tsearch and the problem seems to be this.
> 
> I am using,
> pg_dump in first computer and psql in second computer to execute script.
> 
> First error:
> psql:informatica.sql:24: ERROR:  no se encuentra la función «gtsvector_in» en el archivo «/usr/lib64/pgsql/tsearch2.so»
> 
> Anyone know any way to migrate?

tsearch2 became a built in module, there is doco on the website about 
how to handle this:

http://www.postgresql.org/docs/8.3/static/tsearch2.html

-- 
Postgresql & php tutorials
http://www.designmagick.com/

--

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

25	May 2013
56	April 2013
42	March 2013
95	February 2013
66	January 2013
34	December 2012
47	November 2012
58	October 2012
72	September 2012
30	August 2012
34	July 2012
80	June 2012
105	May 2012
71	April 2012
80	March 2012
104	February 2012
121	January 2012
111	December 2011
87	November 2011
88	October 2011
84	September 2011
127	August 2011
94	July 2011
102	June 2011
180	May 2011
38	April 2011
44	March 2011
126	February 2011
127	January 2011
109	December 2010
99	November 2010
84	October 2010
119	September 2010
91	August 2010
126	July 2010
115	June 2010
147	May 2010
119	April 2010
214	March 2010
113	February 2010
55	January 2010
101	December 2009
84	November 2009
148	October 2009
134	September 2009
153	August 2009
175	July 2009
132	June 2009
114	May 2009
151	April 2009
109	March 2009
108	February 2009
53	January 2009
135	December 2008
168	November 2008
145	October 2008
188	September 2008
142	August 2008
256	July 2008
162	June 2008
133	May 2008
230	April 2008
211	March 2008
236	February 2008
239	January 2008
163	December 2007
164	November 2007
159	October 2007
163	September 2007
258	August 2007
203	July 2007
265	June 2007
214	May 2007
279	April 2007
362	March 2007
288	February 2007
187	January 2007
198	December 2006
295	November 2006
280	October 2006
175	September 2006
254	August 2006
380	July 2006
338	June 2006
209	May 2006
224	April 2006
471	March 2006
331	February 2006
221	January 2006
248	December 2005
286	November 2005
400	October 2005
298	September 2005
319	August 2005
250	July 2005
378	June 2005
269	May 2005
419	April 2005
415	March 2005
363	February 2005
331	January 2005
168	December 2004
300	November 2004
214	October 2004
260	September 2004
348	August 2004
244	July 2004
310	June 2004
222	May 2004
345	April 2004
269	March 2004
385	February 2004
306	January 2004
230	December 2003
346	November 2003
409	October 2003
421	September 2003
348	August 2003
491	July 2003
352	June 2003
589	May 2003
455	April 2003
630	March 2003
609	February 2003
529	January 2003
275	December 2002
446	November 2002
423	October 2002
443	September 2002
420	August 2002
468	July 2002
411	June 2002
330	May 2002
284	April 2002
2	December 2001

Mon	Tue	Wed	Thu	Fri	Sat	Sun

						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28