SEO

During development i stumbled upon a strange behaviour in 8.2.4
Here is the case:

CREATE TYPE testretval AS (tval text);

CREATE OR REPLACE FUNCTION test() RETURNS testretval AS $$
DECLARE
_r record;
retval testretval%ROWTYPE;
BEGIN
SELECT 'test'::character(20) as tc INTO retval;
RETURN retval;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

orderdb_test=# select '-'||tval||'-' from test();
?column?
------------------------
-test -

Why is this still blank padded? Shouldn't a character(20) -> text
conversion
happen implicitly when the value is selected into the return type
that is declared as text?
The casting to text itself seems to work just fine:

orderdb_test=# select '-'||('test'::character(20))::text||'-';
?column?
----------
-test-

I just want to understand a bit better about the internals of how
this works.
should it use this cast when selecting to the return type?

from casts view i see that:
Source type | Target type |
Function | Implicit?
-----------------------------+-----------------------------
+---------------------+---------------
character | text |
text | yes

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)



---------------------------(end of broadcast)---------------------------
TIP 1: 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

Kristo Kaiv <kristo.kaiv@skype.net> writes:
> During development i stumbled upon a strange behaviour in 8.2.4
> ...
> Why is this still blank padded? Shouldn't a character(20) -> text
> conversion
> happen implicitly when the value is selected into the return type
> that is declared as text?

No, that's not how plpgsql deals with forced type conversions.
Its method is always "run the source type's output function, then
feed that to the destination type's input function". In some cases
that will yield different behavior than a SQL cast.

regards, tom lane

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