Re: How to return argument data type from sql function

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Oct 14, 2022 at 2:00 PM Andrus <kobruleht2@xxxxxx> wrote:

I tried

create or replace FUNCTION torus(eevarus bpchar) returns bpchar immutable AS $f$
select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

but it still returns result without trailing spaces. So it is not working.

As was said, only the data type itself was going to be handled, not the length.

 

Another possibility is to have just one function declared
to take and return anyelement.  You'd get failures at
execution if the actual argument type isn't coercible
to and from text (since translate() deals in text) but
that might be fine.

I tried

create or replace FUNCTION torus(eevarus anylement ) returns anylement immutable AS $f$
select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

but got error

type anyelement does not exists.

I'm inclined to believe that your code actually has the same typo you are showing in this email - you spelled anyelement incorrectly.

 

Finally I tried

create or replace FUNCTION torus(eevarus text ) returns text immutable AS $f$
select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
$f$ LANGUAGE SQL ;

create or replace function public.ColWidth(p_namespace text, p_table text, p_field text)
    returns int as $f$
select atttypmod-4 from pg_namespace n, pg_class c, pg_attribute a
         where n.nspname = p_namespace and
             c.relnamespace = n.oid and
             c.relname = p_table and
             a.attrelid = c.oid and
             a.attname = p_field;
$f$ LANGUAGE SQL ;

create table public.test ( charcol char(10) );
insert into test values ('test');
select rpad ( torus(charcol),  colwidth('public', 'test', 'charcol') )
FROM Test

as Adrian Klaver recommends in

https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290

at this worked. In this best solution?

Padding a text typed output with actual significant spaces "works"?  It is not equivalent to a bpchar with insignificant padding spaces...

Using the system catalogs is probably required.  Though I imagine you could create something like: text10 and text20 domains and enforce an explicit length in their constraints.

There isn't too much out there to make this easy - it isn't exactly considered desirable or useful to incorporate blank padding space into data.  Most of us just pretend char(n) doesn't exist.  Frankly, varchar(n) is the same - one can live a long and happy life with just text.

How to remove p_namespace  parameter from colwidth()? ColWidth() should return column width in first search_path table just like  select ... from test finds table test.

Not sure on the full syntax but it probably involves doing something like: table_name::regclass to get the OID and perform the lookup using that.

https://www.postgresql.org/docs/current/datatype-oid.html

David J.