Обсуждение: How to return argument data type from sql function

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 ;

This function is called as CHAR(n) or text columns like

    create temp table test (
    charcol char(10),
    textcol text );
    
    insert into test values ('test', 'test');
    
    select torus(charcol), torus(textcol), charcol

torus(charcol) returns text column and loses original column width. How to force torus() to return argument type:

if char(n) column is passed as argument, torus() should also return char(n) data type.
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.

npgsql DataReader is used to get data.

Andrus.

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

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.

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.

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