[BUGS] Documentation regarding %ROWTYPE in PL/PgSQL

[Andrew McMillan]

I recently referred to the manual (section 23.3) to work out how to
write a PL/PgSQL function that accepted a row as a parameter.

Reading between a few lines I got the impression that the manual
suggested something like:

CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ...

When I finally got my function working, I found I had:

CREATE or REPLACE myfunc( tablename ) RETURNS ...

This is brilliant :-), and in fact the manual foreshadows it:

"although one might expect a bare table name to work as a type
declaration, it won't be accepted within
PL/pgSQL functions."

So I guess the manual needs a little update there, or perhaps the syntax
that I used only works in the variable list, so I was lucky.

I would happily supply a patch to the documentation myself, except that
I don't really know what the correct answer is!  The docs get a bit hazy
in this area regarding the differences between function parameters,
declared variables and declared aliases.

If someone wants to supply me with the lowdown (or tell me who they are
and I can quiz them :-) then I will produce a patch for this area.

I'm doing this under 7.2.1, but I've checked CVS as far as I am able and
this appears not to have changed.

Regards,
Andrew.
-- 

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201MOB: +64(21)635-694OFFICE: +64(4)499-2267
   Are you enrolled at http://schoolreunions.co.nz/ yet?


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

Re: [BUGS] pg_dump && aggregate bug

[Mathieu Arnold]

--On mardi 21 mai 2002 10:19 -0400 Tom Lane <[EMAIL PROTECTED]> wrote:

> Mathieu Arnold <[EMAIL PROTECTED]> writes:
>> when I dump my database, in the dump file, the aggregate becomes :
>
>> CREATE AGGREGATE first ( BASETYPE = text, SFUNC = first_cat, STYPE =
>> text,  INITCOND = '' );
>
> Ooops.  This seems to be fixed already in current sources, but I think
> a back-patch to 7.2 may be warranted.  Try this around line 1912:
>
> agginfo[i].aggbasetype = strdup(PQgetvalue(res, i,
> i_aggbasetype)); -   agginfo[i].agginitval = strdup(PQgetvalue(res,
> i, i_agginitval)); +   if (PQgetisnull(res, i, i_agginitval))
> +   agginfo[i].agginitval = NULL;
> +   else
> +   agginfo[i].agginitval = strdup(PQgetvalue(res, i,
> i_agginitval)); agginfo[i].usename = strdup(PQgetvalue(res, i,
> i_usename));
>
>   regards, tom lane

worked, thanks.
I wonder if it could go into a possible 7.1.2 if there is one ? :)

-- 
Mathieu Arnold

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

Re: [BUGS] Documentation regarding %ROWTYPE in PL/PgSQL

[Tom Lane]

Andrew McMillan <[EMAIL PROTECTED]> writes:
> Reading between a few lines I got the impression that the manual
> suggested something like:
> CREATE or REPLACE myfunc( tablename%ROWTYPE ) RETURNS ...
> When I finally got my function working, I found I had:
> CREATE or REPLACE myfunc( tablename ) RETURNS ...
> This is brilliant :-), and in fact the manual foreshadows it:
> "although one might expect a bare table name to work as a type
> declaration, it won't be accepted within
> PL/pgSQL functions."

IMHO, %ROWTYPE is an Oracle-ism that we support in plpgsql functions
for compatibility's sake.  It should work to just use the name of the
composite type (= name of the table).  But there's at least one place
where plpgsql currently requires the %ROWTYPE marker, though I forget
the details.

The variant that is supported in CREATE FUNCTION argument and result
declarations (outside the function body) is "tablename%TYPE" and
"tablename.fieldname%TYPE".  I have no idea how compatible that is
with Oracle, though I believe it was suggested by someone who wanted
to port Oracle code.

> I would happily supply a patch to the documentation myself, except that
> I don't really know what the correct answer is!  The docs get a bit hazy
> in this area regarding the differences between function parameters,
> declared variables and declared aliases.

I'm not sure either.  A little experimentation seems called for.

regards, tom lane

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

http://archives.postgresql.org

[BUGS] Bug #680: NOCREATETABLE

[pgsql-bugs]

Marcia Abade ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
NOCREATETABLE

Long Description
I saw a artiche what say that in 7.2 PostgreSQL version, the resource of create a user 
with NOCREATETABLE was ready to use.
I´m trying to use this,  but unsucessable... Was it really implemented?

Sample Code


No file was uploaded with this report


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [BUGS] Bug #680: NOCREATETABLE

[Tom Lane]

[EMAIL PROTECTED] writes:
> I saw a artiche what say that in 7.2 PostgreSQL version, the resource of create a 
>user with NOCREATETABLE was ready to use.

I don't know where you saw that, but it has nothing to do with reality.

regards, tom lane

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

http://archives.postgresql.org

[BUGS] views are not auto completed on psql

[Jeff Post]

The best way that I can think to describe this is that views are not added
as candidates of possible line completions in psql.
So if I have a view with the name org_details and a table with the name
organizations and I try select * from org the completion is
organizations not a list of organizations and org_details.


Thanks,
Jeff Post
[EMAIL PROTECTED]


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

Re: [BUGS] Bug #676: lower(), upper(), & initcap() do not work on utf-8 chars

[Henry House]

On Sat, May 25, 2002 at 12:56:06AM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > The string case manipulation functions lower(), upper(), & initcap() 
> > have no effect on non-ASCII characters in the argument, such as �, �, 
> > �, �, etc. ASCII chars in the argument are properly up- or down-cased.
> > The database encoding is UTF-8. 
> 
> lower/upper-casing is driven by locale, not encoding.
> 
> Unfortunately you didn't mention anything about your locale setup...

The server locale is en_US.UTF-8. (At least I set it up as such when
installing PostgreSQL; I know no way to verify.) The server version is 7.2.1,
running on a IA32 and a DEC Alpha; both machines show the same behavior. Both
are Debian Linux. Perhaps the bug lies in the locale definition supplied by
Debian?

-- 
Henry House
The attached file is a digital signature. See 
for information.  My OpenPGP key: .



msg04246/pgp0.pgp
Description: PGP signature

[BUGS] PgLargeObject bug

[Chris Traylor]

see attachment.

-- 
Chris Traylor


If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to [EMAIL PROTECTED]

To report any other bug, fill out the form below and e-mail it to
[EMAIL PROTECTED]

If you not only found the problem but solved it and generated a patch
then e-mail it to [EMAIL PROTECTED] instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.


POSTGRESQL BUG REPORT TEMPLATE



Your name   : Chris Traylor
Your email address  : [EMAIL PROTECTED]


System Configuration
-
  Architecture (example: Intel Pentium) : AMD Athlon

  Operating System (example: Linux 2.0.26 ELF)  : Linux 2.4.10 ELF

  PostgreSQL version (example: PostgreSQL-7.2.1):   PostgreSQL-7.2.1

  Compiler used (example:  gcc 2.95.2)  : gcc 3.1


Please enter a FULL description of your problem:

The method PgLargeObject::LOid() is missing an implementation in the .cc file.

The program seems to compile ok, but when linking a program that uses the call,
g++ chokes with an undefined reference error.



Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible: 
--





If you know how this problem might be fixed, list the solution below:
-
I include the code:

Oid PgLargeObject::LOid(){
  return pgObject;
}

in the .cc file.




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[BUGS] problem with date cast

[andrea gelmini]

hi all,
with latest cvs version of pg i've found this problem:

ns:/tmp> createdb gino
CREATE DATABASE
ns:/tmp> psql gino
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit

gino=# create table test (date date, timestamp timestamp);
CREATE TABLE
gino=# insert into test values ('now','now');
INSERT 32101 1
gino=# \x  
Expanded display is on.
gino=# select *,date::date as right,timestamp::date as wrong from test ;
-[ RECORD 1 ]-
date  | 27/05/2002
timestamp | 27/05/2002 18:17:30,577187
right | 27/05/2002
wrong | 01/01/2000

am i missing something?

some info:
Linux ns 2.4.18-pre9-ac4 #1 lun feb 18 14:24:51 CET 2002 i686 unknown
debian (sid)
 PostgreSQL 7.3devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.1
 (Debian)

all regression tests passed.

thanks a lot for your work,
andrea gelmini

-- 
Jesus died for your sins. Make it worth his time.

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

Re: [BUGS] Bug #676: lower(), upper(), & initcap() do not work on

[Tatsuo Ishii]

> > lower/upper-casing is driven by locale, not encoding.
> > 
> > Unfortunately you didn't mention anything about your locale setup...
> 
> The server locale is en_US.UTF-8. (At least I set it up as such when
> installing PostgreSQL; I know no way to verify.) The server version is 7.2.1,
> running on a IA32 and a DEC Alpha; both machines show the same behavior. Both
> are Debian Linux. Perhaps the bug lies in the locale definition supplied by
> Debian?

I don't think current locale support code works with mutibyte
encodings such as UTF-8. See the thread tiled "Bug #659:
lower()/upper() bug on" on pgsql-bugs and pgsql-hackers.

In the mean time, a work around would be something like:

select convert(lower(convert('X', 'LATIN1')),'LATIN1','UNICODE');

That will convert UTF-8 'X' to its lower case if you are sure that 'X'
could be converted to ISO-8859-1.

Of course the problem with this method is:

Someone has suggested me a fix using UTF-8 locales, but I'm worried
about usage of UTF-8 and am waiting for the test result with my
Japanese data.
--
Tatsuo Ishii

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

Re: [BUGS] views are not auto completed on psql

[Neil Conway]

On Fri, 24 May 2002 17:16:28 -0700
"Jeff Post" <[EMAIL PROTECTED]> wrote:

> The best way that I can think to describe this is that views are not added
> as candidates of possible line completions in psql.
> So if I have a view with the name org_details and a table with the name
> organizations and I try select * from org the completion is
> organizations not a list of organizations and org_details.

On my end, that doesn't seem to be the case (running a build of CVS
that is few days old):

nconway=> create table organizations (a int);
CREATE TABLE
nconway=> create view org_details as select * from organizations;
CREATE VIEW
nconway=> select * from org
org_detailsorganizations  

(where  represents hitting the tab key a couple times)

The query issued by psql is:

SELECT relname FROM pg_class WHERE (relkind='r' or relkind='v') and 
substr(relname,1,3)='org'

... which suggests to me that, at least for the common case, views are
supported for line completion.

Can you give us a reproducible test-case that demonstrates the problem?

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]>
PGP Key ID: DB3C29FC

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

Re: [BUGS] views are not auto completed on psql

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> "Jeff Post" <[EMAIL PROTECTED]> wrote:
>> The best way that I can think to describe this is that views are not added
>> as candidates of possible line completions in psql.

> On my end, that doesn't seem to be the case (running a build of CVS
> that is few days old):

This seems to be a post-7.2 fix.  In psql 7.2 the completion query only
looks for relkind = 'r'.  Might be worth backpatching into the REL7_2
branch?

regards, tom lane

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

http://archives.postgresql.org