Re: How to return argument data type from sql function

On Fri, Oct 14, 2022 at 2:00 PM Andrus <kobruleht2(at)hot(dot)ee> 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.