How to make silently truncate for char type

Actually, I consider it normal behavior of a database to have an error if
the
data value length is greater than the defined length. If it needs to be
truncated, you do
the truncate before you insert into the table. This is the way it works on
enterprise databases like Oracle. Maybe it was a bug in 7.1 that
automatically truncated for you. I don't know for sure as I only have 7.2
installed.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Terence Leung
Sent: Sunday, December 08, 2002 7:02 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How to make silently truncate for char type

I upgraded postgresql 7.1 to 7.2 and found that many
sql cannot execute because version 7.2 does not
silently truncate the string for char(n) type during
insert a new row.
e.g. insert t (field1) value ('abcdef');
where field1 is char(5) but I insert the string
has 6 characters.

How can I solve the problem except downgrade the
Postgresql?

Looking forward to your reply!!!

=====
Yours sincerely,
Leung Chun Man, Terence

Mobile: (852) 9273 9176
Homepage: http://tcmleung.uhome.net ,
http://www.dynamicdrive.com/dynamicindex4/filter/index.htm

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Creating a rule/trigger (before insert) with the appropriate substr
function would, I imagine, do the trick.

Regards,

Aasmund

* Terence Leung <tcmleung@yahoo.com> wrote:

I upgraded postgresql 7.1 to 7.2 and found that many
sql cannot execute because version 7.2 does not
silently truncate the string for char(n) type during
insert a new row.
e.g. insert t (field1) value ('abcdef');
where field1 is char(5) but I insert the string
has 6 characters.

How can I solve the problem except downgrade the
Postgresql?

Looking forward to your reply!!!

=====
Yours sincerely,
Leung Chun Man, Terence

Mobile: (852) 9273 9176
Homepage: http://tcmleung.uhome.net ,
http://www.dynamicdrive.com/dynamicindex4/filter/index.htm

__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46 - Elgesetergt. 26B, N-7030 Trondheim

Is it possible, via a shared library that contains functions used by
postgres, to detect when the library is first loaded and when it is
unloaded?

Also, each postgres process loads its own copy of a library that
contains functions. Is it possible to make a single library instance
shared across all postgres processes? I suspect the answer is no b/c I
assume the postmaster exec()'s postgres, rather than forking, but it
doesn't hurt to ask.

thanks in advance!

eric

First, let me say that table functions are really cool!

Let's say I've defined a table function named: myTableFunc() (written
in C), and that I do this:

select a, b, c
from myTableFunc() as foo (a integer, b varchar(255), c text)
where a = 2;

Is it possible for me to get the FROM, WHERE, and AS ... clauses from
within my table function code? I'd like to do this so my table
function can be a little smarter about what it does and what it returns.

Also, in psql, if I press ^C while a table function is executing, the
function doesn't actually stop, but psql does say "Cancel request
sent". What processes this request? How do I tell postgres that I
want to be notified too?

thanks again!

eric

Eric B.Ridge wrote:

Let's say I've defined a table function named: myTableFunc() (written in
C), and that I do this:

select a, b, c
from myTableFunc() as foo (a integer, b varchar(255), c text)
where a = 2;

Is it possible for me to get the FROM, WHERE, and AS ... clauses from
within my table function code? I'd like to do this so my table function
can be a little smarter about what it does and what it returns.

You can get the information related to the "as foo (a integer, b varchar(255),
c text)" aka column definition clause. It is passed in to the function. See
contrib/tablefunc/tablefunc.c:connectby_text(). The ReturnSetInfo structure
handed to the function includes a copy of the tupdesc formed by the parser
(expectedDesc).

This capability was added so late in the 7.3 developemnt cycle that I don't
think it is documented anywhere other than the source code and some posts Tom
Lane made to the list (although I'll have to look -- he may have snuck it in
somewhere).

As far as the FROM and WHERE clauses, I don't think there is any information
available to the function. Not sure it would be practical or even possible to
change that -- maybe someone else will comment on this.

Joe

On Monday, December 9, 2002, at 01:01 AM, Joe Conway wrote:

Eric B.Ridge wrote:

Let's say I've defined a table function named: myTableFunc() (written
in C), and that I do this:
select a, b, c
from myTableFunc() as foo (a integer, b varchar(255), c text)
where a = 2;
Is it possible for me to get the FROM, WHERE, and AS ... clauses from
within my table function code? I'd like to do this so my table
function can be a little smarter about what it does and what it
returns.

You can get the information related to the "as foo (a integer, b
varchar(255), c text)" aka column definition clause. It is passed in
to the function. See contrib/tablefunc/tablefunc.c:connectby_text().
The ReturnSetInfo structure handed to the function includes a copy of
the tupdesc formed by the parser (expectedDesc).

great! I was snooping through tablefunc.c, but didn't really know what
I was looking for. Thanks!

<snip>

As far as the FROM and WHERE clauses, I don't think there is any
information available to the function. Not sure it would be practical
or even possible to change that -- maybe someone else will comment on
this.

hmm.

eric

Eric B.Ridge <ebr@tcdi.com> writes:

Is it possible, via a shared library that contains functions used by
postgres, to detect when the library is first loaded and when it is
unloaded?

You could have a 'static' variable in the library, that all its
functions check first thing, and set to 1 after checking. That would
be a little clumsy, but would work.

As for unloading, I don't think you can detect it other than running a
function on process exit with an atexit() handler.

Also, each postgres process loads its own copy of a library that
contains functions. Is it possible to make a single library instance
shared across all postgres processes? I suspect the answer is no b/c
I assume the postmaster exec()'s postgres, rather than forking, but it
doesn't hurt to ask.

Shared libraries are not shared memory. Each process has its own copy
of the library data area; though the text (library code) is usually
shared, it's read-only. If you want chared memory then use the SysV
or POSIX shm API.

-Doug

On 9 Dec 2002 at 8:42, Doug McNaught wrote:

Eric B.Ridge <ebr@tcdi.com> writes:

Is it possible, via a shared library that contains functions used by
postgres, to detect when the library is first loaded and when it is
unloaded?

You could have a 'static' variable in the library, that all its
functions check first thing, and set to 1 after checking. That would
be a little clumsy, but would work.

I do not know the postgresql code, but I believe, postgresql must be doing a
dlopen internally. So if you define _init and _fini, they would called while
loading and unloading the library.

At least it is that way on linux. Check with man page of dlopen on the platform
you are running..

HTH

Bye
Shridhar

--
Great American Axiom: Some is good, more is better, too much is just right.

"Eric B.Ridge" <ebr@tcdi.com> writes:

Is it possible for me to get the FROM, WHERE, and AS ... clauses from
within my table function code?

AS: yes, as Joe already mentioned.

FROM/WHERE: No, and I think it would be quite inappropriate for the
function's behavior to depend on any such info. To work correctly,
you'd need to encode virtually a complete understanding of SQL into your
function --- consider cases like outer joins or grouping that intervene
between your function call and the FROM/WHERE clauses. And those are
just the simpler cases.

Also, in psql, if I press ^C while a table function is executing, the
function doesn't actually stop, but psql does say "Cancel request
sent". What processes this request? How do I tell postgres that I
want to be notified too?

You can throw a
CHECK_FOR_INTERRUPTS();
into your main loop whereever it seems safe to be killed by an
interrupt.

regards, tom lane

Joe Conway <mail@joeconway.com> writes:

This capability was added so late in the 7.3 developemnt cycle that I don't
think it is documented anywhere other than the source code and some posts Tom
Lane made to the list (although I'll have to look -- he may have snuck it in
somewhere).

src/backend/utils/fmgr/README has

: If the function is being called as a table function (ie, it appears in a
: FROM item), then the expected tuple descriptor is passed in ReturnSetInfo;
: in other contexts the expectedDesc field will be NULL. The function need
: not pay attention to expectedDesc, but it may be useful in special cases.

Someday that README should be rewritten and merged into the SGML
programmer's guide. Right now, it's essential reading for backend
function authors.

regards, tom lane

FROM/WHERE: No, and I think it would be quite inappropriate for the
function's behavior to depend on any such info. To work correctly,
you'd need to encode virtually a complete understanding of SQL into
your

very true. I could probably make a case for this being useful in
highly specialized functions. But in general, I agree, not a good idea.

I've been working on a dblink clone that speaks JDBC via JNI, and am
just trying to figure out what postgres will (and won't) let me do.

You can throw a
CHECK_FOR_INTERRUPTS();
into your main loop whereever it seems safe to be killed by an
interrupt.

cool! Is this documented somewhere? Did I overlook it?

thanks!

eric

"Eric B. Ridge" <ebr@tcdi.com> writes:

You can throw a
CHECK_FOR_INTERRUPTS();
into your main loop whereever it seems safe to be killed by an
interrupt.

cool! Is this documented somewhere?

Uh, only in src/include/miscadmin.h I'm afraid :=(

regards, tom lane

Tom Lane wrote:

src/backend/utils/fmgr/README has

: If the function is being called as a table function (ie, it appears in a
: FROM item), then the expected tuple descriptor is passed in ReturnSetInfo;
: in other contexts the expectedDesc field will be NULL. The function need
: not pay attention to expectedDesc, but it may be useful in special cases.

Someday that README should be rewritten and merged into the SGML
programmer's guide. Right now, it's essential reading for backend
function authors.

I remember that now. I think I even indicated that I would do the merging into
the programmers guide -- I guess I better get busy :-)

Joe