Horton
Menu

Thread

  1. How to return argument data type from sql function

    Andrus <kobruleht2@hot.ee> — 2022-10-13T20:16:09Z

    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.

  2. Re: How to return argument data type from sql function

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-14T14:55:57Z

    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

  3. Re: How to return argument data type from sql function

    Andrus <kobruleht2@hot.ee> — 2022-10-14T20:59:52Z

    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.

  4. Re: How to return argument data type from sql function

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-14T21:24:20Z

    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

  5. Re: How to return argument data type from sql function

    David G. Johnston <david.g.johnston@gmail.com> — 2022-10-14T21:27:24Z

    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.

  6. Re: How to return argument data type from sql function

    Andrus <kobruleht2@hot.ee> — 2022-10-14T21:48:35Z

    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.

  7. Re: How to return argument data type from sql function

    Andrus <kobruleht2@hot.ee> — 2022-10-14T21:56:33Z

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

  8. Re: How to return argument data type from sql function

    David G. Johnston <david.g.johnston@gmail.com> — 2022-10-14T22:14:05Z

    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.

  9. Re: How to return argument data type from sql function

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-10-14T22:19:39Z

    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