[SQL] bug in working with TEXT constants ?

[sad]

Good day.

AFAIK PostgreSQL provides the type TEXT with 4-byte prefix length
which is distinct to C's zero-terminated  (char *)
That's very good.
Then I expect natural possibility to store texts having zero characters.

try 
SELECT 'abc\0de';
SELECT length('abc\0de');
or insert such a value into another table and then select

you'll see a classical result just like you are using (char *).
i suppose it is a little bug deep inside like using a memcpy() or such...

Am i wrong? 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Using % type in Parameters

[Kumar]

Dear Friends,
 
Clarification about the support for the usage of % TYPE in 
Postgres. I am working with postgres 7.3.4 on RH Linux 7.2. Can I use the %TYPE 
in parameters as in Oracle.
 
CREATE OR REPLACE FUNCTION public.fn_listtitle(varchar, 
varchar)  RETURNS refcursor AS'
DECLARE
    ref         
REFCURSOR;BEGINOPEN ref FOR
   
SELECT "Lookuptype", "Lookupname" 
   
FROM "Lookups"  
   
WHERE "Lookuptype" = $1 AND 
            
                
                
    "Lookup_ID " = $2;END;'  LANGUAGE 'plpgsql' 
VOLATILE;
 
Here can I use as
CREATE OR REPLACE FUNCTION 
public.fn_listtitle(%Lookups.Lookuptype, %Lookups.Lookup_ID)
 
Please shed some light.
 
Regards
Kumar

Re: [SQL] Error message during compressed backup

[Kumar]

Hi Peter Eisentraut,

>>select proowner from pg_proc where proname = 'plpgsql_call_handler';
It gives me an id '101'

While I search for the users in the pg_user, there is no user of id 101
select * from pg_user where usesysid = 101;
No result was fetched.

While I search this way
select * from pg_user where usename like 'postgres'
This give me a result username = postgres usesysid = 1

In comments you said
>> You may want to adjust the owner of the function to a valid user (use
UPDATE).
Do u mean I need to update the table pg_proc, with the following statement
UPDATE pg_proc SET proowner = 1 where proname = 'plpgsql_call_handler';

I am asking this because to ensure not any wrong impact gets into the live
database. Pls advise.

Regards
Kumar
- Original Message - 
From: "Peter Eisentraut" <[EMAIL PROTECTED]>
To: "Senthil Kumar S" <[EMAIL PROTECTED]>
Cc: "psql" <[EMAIL PROTECTED]>
Sent: Friday, October 24, 2003 6:01 PM
Subject: Re: [SQL] Error message during compressed backup


> Senthil Kumar S writes:
>
> > $ $ pg_dump -h 192.xxx.x.xxx -p 5432 -v testdb -f
/home/db_repository/testdb20031023.sql.tar.gz -u -F c
>
> > WARNING: owner of function "plpgsql_call_handler" appears to be invalid
>
> Run
>
> select proowner from pg_proc where proname = 'plpgsql_call_handler';
>
> which gives you the ID of the user that owns this function.  Then run
>
> select * from pg_user;
>
> to get the list of valid users.  You may want to adjust the owner of the
> function to a valid user (use UPDATE).
>
> -- 
> Peter Eisentraut   [EMAIL PROTECTED]
>


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] URGENT!!! changing Column size

[Theodore Petrosky]

why not just

pg_dump dbname > olddb.out
pico olddb.out

edit the section that defines the table
save and exit

dropdb dbname
createdb dbname

psql dbname < olddb.out

no fuss no muss...

Ted

--- [EMAIL PROTECTED] wrote:
> Hi can we change the size of a column in postgres. I
> have a table named
> institution and column name is name varchar2(25), i
> want to change it to
> varchar2(50). Please let me know.
> 
> --Mohan
> 
> 
> 
> 
> 
> 
> ---(end of
> broadcast)---
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] URGENT!!! changing Column size

[Franco Bruno Borghesi]

Dopping the whole database just for a column change? 

On Tue, 2003-10-28 at 10:00, Theodore Petrosky wrote:

why not just

pg_dump dbname > olddb.out
pico olddb.out

edit the section that defines the table
save and exit

dropdb dbname
createdb dbname

psql dbname < olddb.out

no fuss no muss...

Ted

--- [EMAIL PROTECTED] wrote:
> Hi can we change the size of a column in postgres. I
> have a table named
> institution and column name is name varchar2(25), i
> want to change it to
> varchar2(50). Please let me know.
> 
> --Mohan
> 
> 
> 
> 
> 
> 
> ---(end of
> broadcast)---
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

__
Do you Yahoo!?
Exclusive Video Premiere - Britney Spears
http://launch.yahoo.com/promos/britneyspears/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html






signature.asc
Description: This is a digitally signed message part

Re: [SQL] URGENT!!! changing Column size

[D'Arcy J.M. Cain]

On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote:
> Dopping the whole database just for a column change?

I guess some people have really small databases that don't take 3 days to dump 
and reload.  :-)

-- 
D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP)   |  what's for dinner.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] URGENT!!! changing Column size

[Christoph Haller]

> 
> On Tuesday 28 October 2003 08:28, Franco Bruno Borghesi wrote:
> > Dopping the whole database just for a column change?
> 
> I guess some people have really small databases that don't take 3 days to dump 
> and reload.  :-)
> 
And you are on the safe side regarding indexes, views, procedures, ...
Regards, Christoph 


---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] numeric and float converts to int differently?

[SZŰCS Gábor]

Dear Gurus,

I'm using PostgreSQL 7.3.3.

I was wondering if this is undefined, intentional or to be changed/fixed in
the future. See the snippit at the end of this mail.

QUESTION 1: Is it intentional that converting 0.5 to int4
- from numeric: rounds *away from* zero
- from float: rounds *towards* zero (tried float4 and float8 too)?

QUESTION 2: Is it safe to assume it won't change (haven't changed) in the
upcoming versions?

QUESTION 3: Is there a function or conversion method that converts 0.5
according to
- mathematical rules (*up*)
- accounting rules (*down*, at least here in Hungary)?

TIA,

G.
--- cut here ---
# select int4('0.5'::numeric);
1
# select int4('-0.5'::numeric);
   -1
# select int4('0.5'::float);
0
# select int4('-0.5'::float);
0
--- cut here ---
What I would really like to see:
# select round_math('0.5');
1
# select round_math('-0.5');
0
# select round_acct('0.5');
0
# select round_acct('-0.5');
   -1
--- cut here ---


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] bug in working with TEXT constants ?

[Stephan Szabo]

On Tue, 28 Oct 2003, sad wrote:

> Good day.
>
> AFAIK PostgreSQL provides the type TEXT with 4-byte prefix length
> which is distinct to C's zero-terminated  (char *)
> That's very good.
> Then I expect natural possibility to store texts having zero characters.

If you want to store zero characters (or binary data) you
probably want bytea not text.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] bug in working with TEXT constants ?

[Tom Lane]

sad <[EMAIL PROTECTED]> writes:
> Then I expect natural possibility to store texts having zero characters.

You expect wrong; we don't support embedded nulls in text values, nor in
literal strings.  You can store nulls in BYTEA fields, but you have to
use bytea's escaping conventions to represent the value in SQL commands,
eg, 'foo\\000bar'.

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

Re: [SQL] numeric and float converts to int differently?

[Tom Lane]

"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <[EMAIL PROTECTED]> writes:
> QUESTION 1: Is it intentional that converting 0.5 to int4
> - from numeric: rounds *away from* zero
> - from float: rounds *towards* zero (tried float4 and float8 too)?

Numeric currently rounds away from zero.  We cannot guarantee to make
it work the same as float, because float's rounding behavior is
platform-dependent.  A common but not universal behavior is "round to
nearest even integer".  See the recent thread about the round() function
(I forget which list it was in, but it was within the past week).

> QUESTION 2: Is it safe to assume it won't change (haven't changed) in the
> upcoming versions?

Numeric's behavior hasn't changed as of 7.4beta5.  I do not have a
crystal ball to predict whether we might change it in the future.
I doubt we will ever try to override the platform behavior for float.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] numeric and float converts to int differently?

[SZŰCS Gábor]

Dear Ken,

reply goes to the list if you don't mind :)

- Original Message - 
From: "Kenneth Marshall" <[EMAIL PROTECTED]>
To: "SZŰCS Gábor" <[EMAIL PROTECTED]>
Sent: Tuesday, October 28, 2003 4:45 PM


> Postgres is using the standard definition of rounding. What you

The "standard" definition I know states that 0.5 should round *up*, not
*away from zero*. i.e. 3.5 rounds (up) to 4, and -3.5 rounds (up) to -3. I
was just wondering if there is an explicit/official claim that Postgres does
round away from zero.

> cannot see is that the float values are not actually exactly 0.5

Yes I could guess that (floating point vs fixed), but is this a coincidence
that both '0.5'::float and '-0.5'::float are closer to 0, whereas they could
be closer to +/-1, as well as both closer to the lower or upper bound.

This is why I asked if it's intentional/guaranteed, or undefined -- say, a
future change in glibc may cause this to change.

> I cannot imagine that the behavior would ever change.

If you can tell the developers' opinion for sure, that's enough for me :)

G.
--- cut here ---


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] URGENT!!! changing Column size

[SZUCS Gábor]

I have a php script that patches database, comparing pg_catalog's tables to
input files. One thing it can do, but I can't take responsibility ;) is
changing the type of a column.

It's basically the same that everyone wrote, except that I also examine
dependencies, as broad an examination as I could think about.

However; the code is not yet ready, and not my exclusive possession, but the
steps I could tell you are:

1. Note oid:
   SELECT oid, relname from pg_class WHERE relname='institution'

2. Identify the attribute:
   SELECT * from pg_attribute WHERE attrelid = xxx AND ...

3. Search for depends:
   SELECT d.*
   FROM pg_depend d, pg_attribute a
   WHERE refobjid=a.attrelid and refobjsubid=a.attnum
 AND ...
   -- you are on your own here ;)

4. Drop those dependencies (more likely, foreign keys, constraints, indexes)

5. Do the change

6. Apply dependencies.

HTH,

G.
--- cut here ---
- Original Message - 
From: <[EMAIL PROTECTED]>
Sent: Monday, October 27, 2003 6:10 PM


> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
>
> --Mohan


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] numeric and float converts to int differently?

[Greg Stark]

"SZÛCS Gábor" <[EMAIL PROTECTED]> writes:

> > cannot see is that the float values are not actually exactly 0.5
> 
> Yes I could guess that (floating point vs fixed), but is this a coincidence
> that both '0.5'::float and '-0.5'::float are closer to 0, whereas they could
> be closer to +/-1, as well as both closer to the lower or upper bound.

Wouldn't 0.5 and -0.5 be representable exactly as floats?


-- 
greg


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] numeric and float converts to int differently?

[SZUCS Gábor]

- Original Message - 
From: "Greg Stark" <[EMAIL PROTECTED]>
Sent: Tuesday, October 28, 2003 7:21 PM


> > > cannot see is that the float values are not actually exactly 0.5
> >
> > Yes I could guess that (floating point vs fixed), but is this a
coincidence
> > that both '0.5'::float and '-0.5'::float are closer to 0, whereas they
could
> > be closer to +/-1, as well as both closer to the lower or upper bound.
>
> Wouldn't 0.5 and -0.5 be representable exactly as floats?

As Tom pointed out, it isn't a floating point failure -- it is how rounding
float is implemented. I assume anything with less than 15 digits can be
exactly represented as float.

G.
--- cut here ---


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] numeric and float converts to int differently?

[Bruno Wolff III]

On Tue, Oct 28, 2003 at 21:16:53 +0100,
  SZUCS Gábor <[EMAIL PROTECTED]> wrote:
> 
> As Tom pointed out, it isn't a floating point failure -- it is how rounding
> float is implemented. I assume anything with less than 15 digits can be
> exactly represented as float.

No. It has to be a dyadic number. For example .3 will not be represented
exactly by a float.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] numeric and float converts to int differently?

[Christopher Browne]

[EMAIL PROTECTED] (SZUCS Gábor) writes:
> As Tom pointed out, it isn't a floating point failure -- it is how rounding
> float is implemented. I assume anything with less than 15 digits can be
> exactly represented as float.

No, "decimal" fractions cannot ever be exactly represented in floating
point because since they use powers of two, you wind up with repeated
fractions.

1/3 is approximately 0.333, but you cannot present that exactly in
decimal.

In the very same way, 1/10 is approximately equal to
0.001001001001001001001001001001 (as a binary 'fraction'); the '001'
part is a repeating group, and wherever you terminate it, you lose
exactness.
-- 
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))

Christopher Browne
(416) 646 3304 x124 (land)

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] SOLVED: Emulating 'connect by prior' using stored proc

[Merrall, Graeme]

First off, props for this go to Chris Wenham from his Live Journal post
http://www.livejournal.com/users/terrulen/6008.html. He's happy for me
to pass on the secrets :)

Standard disclaimer applies but it worked well for me with no mods to
our table structure although I had to adjust the function that contained
the code to allow for the fact that the function returns the actual node
you start with whereas Oracle does not. I cheated and used array_shift()
in PHP. YMMV.

Problem: Hosting company who would never in a million years let you
install tablefunc and you needed to help your application ported from
Oracle working nicely.


Solution: postgreSQL 7.3 now has lovely additions to stored procedures
including returning row sets and recursion.

Our table looked like:
 NameNull?Type
 ---  ---
 PARENT_NODE_ID  NOT NULL NUMBER
 CHILD_NODE_ID   NOT NULL NUMBER
 ORDINAL NUMBER

And the Oracle query was:
select child_node_id, level
from node_relationships
connect by prior child_node_id = parent_node_id
start with parent_node_id=682904
order by Hierarchy.Branch(level, ordinal)


In order to make this work with postgres an additional table is needed
that can hold the level (depth) of the branch because pgsql doesn't like
returning a tuple that isn't based on a defined structure. Once you've
created this table you can pretty much forget about it.

CREATE TABLE "node_relationships_n_level" (
"level" integer
) inherits (node_relationships);


Now create your stored procedure. 

CREATE OR REPLACE FUNCTION "crawl_tree" (integer,integer) RETURNS SETOF
node_relationships_n_level AS 'DECLARE
temp RECORD;
child RECORD;
BEGIN
  SELECT INTO temp *, $2 AS level FROM node_relationships WHERE
child_node_id = $1;

  IF FOUND THEN
RETURN NEXT temp;
  FOR child IN SELECT child_node_id FROM node_relationships WHERE
parent_node_id = $1 ORDER BY ordinal LOOP
FOR temp IN SELECT * FROM crawl_tree(child.child_node_id, $2 +
1) LOOP
RETURN NEXT temp;
END LOOP;
  END LOOP;
   END IF;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';


The second parameter must be zero. This is a kludge because this is a
recursive function and I needed some way of passing the level to
successive function calls. However, if you like, you could consider this
to be a "level offset"--set it to '2' and all the levels returned will
be n + 2.

Execute "SELECT * FROM crawl_tree(682904,0)" and you're done.

Hope this helps people.

Cheers,
 Graeme

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] create type input and output function examples

[Clint Stotesbery]

I've seen the docs for create type and an example of the syntax to create a 
type. What I haven't seen is the functions that are passed for the input and 
output elements.

CREATE TYPE box (INTERNALLENGTH = 8,
   INPUT = my_procedure_1, OUTPUT = my_procedure_2);
Now what would be in my_procedure1 and my_procedure2 procedures? I know they 
are just example placeholders above but I need to see a real example of what 
would be in those procedures to better understand what they should do. 
Thanks.

_
Surf and talk on the phone at the same time with broadband Internet access. 
Get high-speed for as low as $29.95/month (depending on the local service 
providers in your area).  https://broadband.msn.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] connectby

[BenLaKnet]

I use postgresql 7.2.3 
How can I use connectby ?? 

Must I install files ? or packages ? or it is recommanded to upgrade
dataserver ?




George Essig wrote:

  
hi

I have menu table:
  id  | integer | not null default 
nextval('public.menu_id_seq'::text)
  parent_id   | integer |
  description | text|

I do select:
test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id 
integer, parent_id integer, level int, branch text);
  id | parent_id | level |   branch
+---+---+-
   2 |   | 0 | 2
   4 | 2 | 1 | 2~4
   7 | 4 | 2 | 2~4~7
  10 | 7 | 3 | 2~4~7~10
  16 |10 | 4 | 2~4~7~10~16
   9 | 4 | 2 | 2~4~9

How am I able to select description file from menu table, not only id, 
parent_id, level, branch fields?

-- 
WBR, sector119

  
  
Try a join with the original table:

SELECT t.*, description FROM connectby('menu','id','parent_id','2',0,'~') 
AS t(id integer, parent_id integer, level int, branch text), menu 
WHERE t.id = menu.id

George Essig

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

  

Re: [SQL] connectby

[Joe Conway]

BenLaKnet wrote:
I use postgresql 7.2.3
How can I use connectby ??
Must I install files ? or packages ? or it is recommanded to upgrade 
dataserver ?
You need to upgrade. Either install 7.3.4 or wait a few weeks and 
install 7.4 when it is released.

Joe



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings