Обсуждение: 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
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
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.
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 Testas Adrian Klaver recommends in
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.
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.
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.
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 <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