How to return argument data type from sql function

Andrus <kobruleht2@hot.ee> writes:

PostgreSQL 12.2+ function is defined as
    create FUNCTION torus(eevarus text) returns text immutable AS $f$
     select translate( $1, U&'\00f8\00e9', U&'\0451\0439' );
    $f$ LANGUAGE SQL ;

if char(n) column is passed as argument, torus() should also return
char(n) data type.

You can't preserve the length constraint, if that's what you're worried
about; we simply don't track those for function arguments or results.

I tried to use bpchar instead on text

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

torusbpchar(charcol) still returns text data type.

Making separate functions for text and bpchar works for me.

regression=# select pg_typeof(torus(f1)) from char_tbl;
pg_typeof
-----------
character

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.

regards, tom lane

Hi!

Making separate functions for text and bpchar works for me.

regression=# select pg_typeof(torus(f1)) from char_tbl;
pg_typeof
-----------
character

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 ;

create temp table test (
charcol char(10) );
insert into test values ('test');
select torus(charcol)
FROM Test

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

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.

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?

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.

Andrus.

Andrus <kobruleht2@hot.ee> writes:

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 I said, width constraints don't propagate through functions.

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.

Might've helped to spell "anyelement" correctly ;-). However, if you're
insistent on those trailing spaces, this approach won't change anything
about that.

select rpad ( torus(charcol),  colwidth('public', 'test', 'charcol') )
FROM Test

Yeah, you could do that if you have the column information at hand.

How to remove p_namespace parameter from colwidth()?

select atttypmod-4 from pg_attribute
where attrelid = p_table::regclass and attname = p_field

Personally I'd also throw in "... and atttypid = 'bpchar'::regtype",
because that atttypmod calculation will give you garbage for types
other than bpchar and varchar.

regards, tom lane

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

Hi!

Yeah, you could do that if you have the column information at hand.
Personally I'd also throw in "... and atttypid = 'bpchar'::regtype",
because that atttypmod calculation will give you garbage for types
other than bpchar and varchar.

I added this:

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
    atttypid = 'bpchar'::regtype and
    a.attname = p_field;
$f$ LANGUAGE SQL ;

Tables with same name are in different schemas.

How to change this query so that it searches schemas in set search_path
order and returns column width from it ? In this case p_namespace
parameter can removed.

Or should it replaced with dynamic query like

execute 'select ' || p_field || ' from ' || p_table || ' limit 0'

and get column size from this query result somehow ?

Andrus.

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...

You are right. I need char(n) type and this is not working.  How to use
expression in cast, like

select torus(charcol) :: CHAR( ColWidth('public', 'test', 'charcol')  )
from test

This throws error in Postgres. ColWidth is immutable and called with
constant arguments so it should work. How to fix postgres to allow
constant ColWidth() expression in cast ?

Andrus.

On Fri, Oct 14, 2022 at 2:56 PM Andrus <kobruleht2@hot.ee> wrote:

select torus(charcol) :: CHAR( ColWidth('public', 'test', 'charcol') )
from test

This throws error in Postgres. ColWidth is immutable and called with
constant arguments so it should work. How to fix postgres to allow constant
ColWidth() expression in cast ?

ColWidth is NOT IMMUTABLE, your declaration of that property is a lie (the
function in your email actually defines it as volatile though...). It is
STABLE.

You are basically stuck dealing with this one layer up, outside the
server. You would need to execute ColWidth then write the returned value
of the function call into the text body of the SQL Command.

David J.

Andrus <kobruleht2@hot.ee> writes:

How to change this query so that it searches schemas in set search_path
order and returns column width from it ? In this case p_namespace
parameter can removed.

I showed you that already: regclass will take care of it.

regards, tom lane