Re: [SQL] SQL CASE Statements
> Has anybody done this? If so, can you send me a sample? CREATE TEMPORARY TABLE fruits (id SERIAL, name TEXT); INSERT INTO fruits VALUES (DEFAULT, 'banana'); INSERT INTO fruits VALUES (DEFAULT, 'apple'); CREATE TEMPORARY TABLE food (id SERIAL, name TEXT); INSERT INTO food VALUES (DEFAULT, 'apple'); INSERT INTO food VALUES (DEFAULT, 'spinach'); SELECT name, CASE WHEN name = ANY (SELECT name FROM fruits) THEN 'yes' ELSE 'no' END AS fruit FROM food; name | fruit -+--- apple | yes spinach | no (2 lines) __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Problem with self-made plpgsql-function / casting
Hello, I'm a newbie to postgres and trying to produce my first functions. Everything works fine as long as it takes numeric parameters, but when I'm trying to use varchar or text-parameters, it fails and throws the following exception: ERROR: function public.fc_editlanguage(integer, "unknown", "unknown", integer) does not existHINT: No function matches the given name and argument types. You may need to add explicit type casts. So I thought I would have to use the pgfunction cast(parameter as type) before working with the parameter within the function. Still, the same exception is thrown and I haven't got a clue how to solve this problem. Hope someone can help me out, here is my written function: CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name varchar, kuerzel varchar, active smallint) RETURNS smallint AS$body$DECLARE id bigint;DECLARE varlanguage varchar(60); DECLARE browsershortcut varchar(10);DECLARE insertdate date;DECLARE active smallint; DECLARE varreturn smallint; Begin varreturn := 0; id := $1; varlanguage := cast($2 as varchar(60)); bowsershortcut := cast($3 as varchar(10)); active := $4; if(id=0) then insertdate := now(); INSERT INTO tbl_language (la_language, la_browsershortcut, la_insertdate, la_active) VALUES (varlanguage, browsershortcut, insertdate, active); else UPDATE tbl_language SET la_language=varlanguage, la_browsershortcut=browsershortcut, la_active=active WHERE la_id = id; end if; return varreturn;end;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Greetings from Germany, Moritz PS: I'm using postgres 8.0 on a xp system
Re: [SQL] Problem with self-made plpgsql-function / casting
Moritz Bayer <[EMAIL PROTECTED]> writes: > ERROR: function public.fc_editlanguage(integer, "unknown", "unknown", > integer) does not exist > > CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name > varchar, kuerzel varchar, active smallint) RETURNS smallint AS The short answer to this is to avoid declaring function arguments as "smallint". When you call this as, say, select fc_editlanguage(42, 'foo', 'bar', 1); the "42" and the "1" are initially typed as integer constants. There's an implicit up-cast from integer to bigint, so the parser has no problem matching the 42 to a bigint parameter, but the down-cast from integer to smallint is not implicit. With the function as written you'd have to cast to smallint explicitly: select fc_editlanguage(42, 'foo', 'bar', 1::smallint); This is enough of a notational pain in the neck that it's easier just to declare the argument as integer. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Problem with self-made plpgsql-function / casting
Moritz, The issue is most likely with the passed parameters themselves, not the assignments within the function. When you call the function, be sure to cast arguments that don't comply with your function definition. For example: Look at your function call. Does it look like this? select fc_editlanguage(123, 'some name', 'some text value', 456) What you need to do is be more specific with the types being passed. Try this instead: select fc_editlanguage(123, 'some name'::varchar, 'some text value'::varchar, 456) Best of luck, Mark On Sat, 2005-08-20 at 16:54 +0200, Moritz Bayer wrote: > Hello, > > I'm a newbie to postgres and trying to produce my first functions. > Everything works fine as long as it takes numeric parameters, but when > I'm trying to use varchar or text-parameters, it fails and throws the > following exception: > > ERROR: function public.fc_editlanguage(integer, "unknown", "unknown", > integer) does not exist > HINT: No function matches the given name and argument types. You may > need to add explicit type casts. > > So I thought I would have to use the pgfunction cast(parameter as > type) before working with the parameter within the function. Still, > the same exception is thrown and I haven't got a clue how to solve > this problem. Hope someone can help me out, here is my written > function: > > > CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name > varchar, kuerzel varchar, active smallint) RETURNS smallint AS > $body$ > DECLARE id bigint; > DECLARE varlanguage varchar(60); > DECLARE browsershortcut varchar(10); > DECLARE insertdate date; > DECLARE active smallint; > > DECLARE varreturn smallint; > > Begin > varreturn := 0; > id := $1; > varlanguage := cast($2 as varchar(60)); > bowsershortcut := cast($3 as varchar(10)); > active := $4; > if(id=0) then > insertdate := now(); > INSERT INTO tbl_language (la_language, la_browsershortcut, > la_insertdate, la_active) > VALUES > (varlanguage, browsershortcut, insertdate, active); > else > UPDATE tbl_language SET la_language=varlanguage, > la_browsershortcut=browsershortcut, la_active=active > WHERE la_id = id; > end if; > return varreturn; > end; > $body$ > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; > > > Greetings from Germany, > Moritz > > > PS: I'm using postgres 8.0 on a xp system > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Problem with self-made plpgsql-function / casting
Thanks for your reply, but your tip didn't solve my problem. I used your function call: select fc_editlanguage(0, 'German'::varchar, 'de'::varchar, 1); but still got an error: ERROR: function fc_editlanguage(integer, character varying, character varying, integer) does not existHINT: No function matches the given name and argument types. You may need to add explicit type casts. At least, as you pointed out, the passed parameters are recognized as character varying and not as unknown! So I guess I'm one step further, but haven't reached my goal. Do you have another hint for me? Best regards, Moritz 2005/8/20, Mark Dingee <[EMAIL PROTECTED]>: Moritz,The issue is most likely with the passed parameters themselves, not theassignments within the function. When you call the function, be sure to cast arguments that don't comply with your function definition. Forexample:Look at your function call. Does it look like this?select fc_editlanguage(123, 'some name', 'some text value', 456) What you need to do is be more specific with the types being passed.Try this instead:select fc_editlanguage(123, 'some name'::varchar, 'some textvalue'::varchar, 456)Best of luck,Mark On Sat, 2005-08-20 at 16:54 +0200, Moritz Bayer wrote:> Hello,>> I'm a newbie to postgres and trying to produce my first functions.> Everything works fine as long as it takes numeric parameters, but when > I'm trying to use varchar or text-parameters, it fails and throws the> following exception:>> ERROR: function public.fc_editlanguage(integer, "unknown", "unknown",> integer) does not exist > HINT: No function matches the given name and argument types. You may> need to add explicit type casts.>> So I thought I would have to use the pgfunction cast(parameter as> type) before working with the parameter within the function. Still, > the same exception is thrown and I haven't got a clue how to solve> this problem. Hope someone can help me out, here is my written> function:>>> CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name > varchar, kuerzel varchar, active smallint) RETURNS smallint AS> $body$> DECLARE id bigint;> DECLARE varlanguage varchar(60);> DECLARE browsershortcut varchar(10);> DECLARE insertdate date; > DECLARE active smallint;>> DECLARE varreturn smallint;>> Begin> varreturn := 0;> id := $1;> varlanguage := cast($2 as varchar(60));> bowsershortcut := cast($3 as varchar(10)); > active := $4;> if(id=0) then> insertdate := now();> INSERT INTO tbl_language (la_language, la_browsershortcut,> la_insertdate, la_active)> VALUES > (varlanguage, browsershortcut, insertdate, active);> else> UPDATE tbl_language SET la_language=varlanguage,> la_browsershortcut=browsershortcut, la_active=active> WHERE la_id = id; > end if;> return varreturn;> end;> $body$> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;>>> Greetings from Germany,> Moritz >>> PS: I'm using postgres 8.0 on a xp system>-- http://ad.zanox.com/ppv/?2510394C569771607 " align="bottom" width="1" height="1" border="0" hspace="1">http://ad.zanox.com/ppc/?2510394C569771607T" >Lenscare AG - Europas größter Kontaktlinsenversand
Re: [SQL] Problem with self-made plpgsql-function / casting
That's it!!! Thanks a lot! Moritz PS: Hope some day I'll be the one to be an help for newbies! I'm working on it! 2005/8/20, Tom Lane <[EMAIL PROTECTED]>: Moritz Bayer <[EMAIL PROTECTED]> writes: > ERROR: function public.fc_editlanguage(integer, "unknown", "unknown",> integer) does not exist>> CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name > varchar, kuerzel varchar, active smallint) RETURNS smallint ASThe short answer to this is to avoid declaring function arguments as"smallint". When you call this as, say, select fc_editlanguage(42, 'foo', 'bar', 1); the "42" and the "1" are initially typed as integer constants. There'san implicit up-cast from integer to bigint, so the parser has no problemmatching the 42 to a bigint parameter, but the down-cast from integer to smallint is not implicit. With the function as written you'd have tocast to smallint explicitly: select fc_editlanguage(42, 'foo', 'bar', 1::smallint);This is enough of a notational pain in the neck that it's easier just to declare the argument as integer. regards, tom lane-- http://ad.zanox.com/ppv/?2510394C569771607 " align="bottom" width="1" height="1" border="0" hspace="1">http://ad.zanox.com/ppc/?2510394C569771607T" >Lenscare AG - Europas größter Kontaktlinsenversand
[SQL] Data insert
Sorry for the attachment, but copying and pasting this data does not work. I don't have any idea how to insert the type of data into PostgreSQL. Basically, it's encrypted data in which I would like that keep raw format. Thanks for any help, J sample_data.pdf Description: Adobe PDF document ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [ADMIN] Data insert
On Sat, Aug 20, 2005 at 01:17:55PM -0400, Postgres Admin wrote: > I don't have any idea how to insert the type of data into PostgreSQL. > Basically, it's encrypted data in which I would like that keep raw format. If the data is binary then you could store it in a BYTEA column. How to get such data into the database depends on the client interface -- you could escape the data appropriately and embed it into an INSERT statement, or you could use a parameterized query that either transfers binary data using the PostgreSQL protocol (available in 7.4 and later) or automatically escapes the data. See your interface documentation for more information. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [ADMIN] Data insert
1st
---
if you use php you can use the function:
"pg_escape_bytea()"
when you want to retrieve the data from db you have to use:
"pg_unescape_bytea()"
for unescaping it
see the php documentation on this function for more information
2nd
---
if you want to insert biiig data volumes try either using COPY instead
of INSERT - it will run much much faster
you can use implement somewhere this function to use it very easy in php:
/***
$tableName - specifies the name of the table where the data has to be
copied into
$copyArr- contains "n" elements of rows to be inserted, sample
element:
array(
"col1"=> "foo",
"col2"=> "bar"
)
***/
function copyInto($tableName,$copyArr) {
$thisDBConn = $this->getThisDBConnection(); // replace here your
retrieve to get your db connection into this variable
$queryCopyStart = "COPY ".$tableName." (";
// get out columns that has to be processed
$columnList = "";
$count = 0;
foreach ($copyArr[0] as $key=>$value) {
$count++;
if ($count!=1) {
$columnList .= ",";
}
$columnList .= $key;
}
$queryCopyStart .= $columnList.") FROM STDIN WITH DELIMITER AS '\\t'
NULL AS '' CSV QUOTE AS '\\'' ESCAPE AS '';\n";
$queryCopyData = Array();
$countLine = 0;
foreach ($copyArr as $lineKey=>$lineValue) {
$countLine++;
$thisLine = "";
$countData = 0;
foreach ($lineValue as $dataKey=>$dataValue) {
$countData++;
$thisLine .= "'".$dataValue."'";
if ($countData!=count($lineValue)) {
$thisLine .= chr(9);
}
}
$thisQueryCopyData = $thisLine."\n";
array_push($queryCopyData,$thisQueryCopyData);
}
$queryCopyEnd = "\\.\n";
pg_query($thisDBConn,$queryCopyStart);
pg_put_line($thisDBConn,implode("",$queryCopyData));
pg_put_line($thisDBConn,$queryCopyEnd);
pg_end_copy($thisDBConn);
}
be aware with the "max_stack_depth" value in postgresql.conf, maybe you
will need to increase it
Postgres Admin wrote:
Sorry for the attachment, but copying and pasting this data does not work.
I don't have any idea how to insert the type of data into PostgreSQL.
Basically, it's encrypted data in which I would like that keep raw format.
Thanks for any help,
J
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
