[SQL] oracle decode()

[Andreas Schmitz]

Hello,

is there any equivalent for ORACLEs decode() in pl/pgsql ?

regards,

-Andreas


-- 



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] oracle decode()

[Richard Huxton]

On Wednesday 03 March 2004 10:48, Andreas Schmitz wrote:
> Hello,
>
> is there any equivalent for ORACLEs decode() in pl/pgsql ?

Is that the function that works like CASE?

CASE WHEN x=1 THEN 'ONE' WHEN x=2 THEN 'TWO' END

See the reference manual for syntax (conditional functions/operators iirc).

-- 
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[SQL] Encoding bytea

[Achilleus Mantzios]

Hi,
is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for 
usage in an insert statement?

None of base64,hex,escape options in encode() seem to produce
anything close.

This is meant to be used with generating insert statements in xml files
for remote processing.

I can always (in java) ResultSet.getString(bytea_col_idx), 
get the result (ala psql) with one "\" trimmed out,
and process the output (by adding an extra "\" where needed) from there, 
but i am not sure if the jdbc driver will be allowing this ... convinience
in the future.

Thanx.

-- 
-Achilleus


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Encoding bytea

[Joe Conway]

Achilleus Mantzios wrote:

is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for 
usage in an insert statement?

None of base64,hex,escape options in encode() seem to produce
anything close.
This is meant to be used with generating insert statements in xml files
for remote processing.
Is this what you need?

create table t(f bytea);
insert into b values ('a\\003\\000\\001b');
create or replace function bytea2text(bytea) returns text as '
begin
 return $1;
end;
' language plpgsql;
regression=# select 'insert into t values(' || 
quote_literal(bytea2text(f)) || ');' from t;
  ?column?

 insert into t values('a\\003\\000\\001b');
(1 row)

regression=# insert into t values('a\\003\\000\\001b');
INSERT 292656 1
HTH,

Joe

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] calling function

[William Anthony Lim]

Hi,
I have a little trouble. I'm newbie in postgresql.

Consider this function example:

create or replace function testcall(int4,varchar,bool,int2) return setof record as '
declare
r record;
a int4;
b varchar;
c bool;
d int2;
begin
a=$1;
b=$2;
c=$3;
d=$4;

for r in select * from "T_Customer" loop
return next r;
end loop;

return r;
end;'
language 'plpgsql'


when i tried to call it using:
select * from testcall(12,'ABCD',true,2);

it says:
ERROR:  function testcall(integer, "unknown", boolean, integer) does not exist

then I tried to change to:
select * from testcall(12,varchar'ABCD',true,2);

it says:
ERROR:  function testcall(integer, character varying, boolean, integer) does not exist

I've tried them using jdbc prepared statement and callable statement (both 
with/without parameters), but the result is the same.
what should i do?

Thanks

William


Need a new email address that people can remember
Check out the new EudoraMail at
http://www.eudoramail.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] calling function

[Tom Lane]

"William Anthony Lim" <[EMAIL PROTECTED]> writes:
> create or replace function testcall(int4,varchar,bool,int2) return setof record as '
> ...
> select * from testcall(12,'ABCD',true,2);
> ERROR:  function testcall(integer, "unknown", boolean, integer) does not exist

An undecorated integer constant is considered int4 (or int8 or numeric
if large enough), and there's no automatic downcast to int2.  You could
write 2::int2 or some such, but on the whole I'd recommend declaring
the function to take int4 not int2.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org