[SQL] MD5 function is not available ?
Hey, I've searched for MD5 crypting function in PG, but I did not find it. Anyone knows how to implement this function in PG ? Best wishes, Marek L. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] plPGSQL bug in function creation
Hello,
I think that there is a bug in plPGSQL - or maybe I don't know something
about this language. Try to create this function
Ok., this is the function created in plPGSQL:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
If you will execute SELECT test('tess', 'erer') -> then "tess" will be
returned. If you will execute: SELECT test(NULL, 'buuu'); -> then it
will return NULL, but it should return "buuu". I tried to figure out why
it is happening so i modifye this function to this:
CREATE FUNCTION "public"."test" (text, text) RETURNS text AS'
BEGIN
RETURN 'test';
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
And when i execute: SELECT test(NULL, 'buuu'); -> it returns me NULL
value, when it should return "buuu". Well I think that something is
wrong here.
If I will modify this function again to this:
CREATE FUNCTION "public"."test" (varchar, varchar) RETURNS text AS'
BEGIN
IF $1 THEN
RETURN $1;
ELSE
RETURN $2;
END IF;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Everything is working OK.. So the problem is in TEXT type definition.
I'm using PG 7.3.1 on Win/Cyg
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] MD5() function not available ??
Hey, I've searched for MD5 crypting function in PG, but I did not find it. Anyone knows how to implement this function in PG ? Best wishes, Marek L. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] SQL query problem (many table in FROM statement and many LEFT JOIN's)
> > SELECT
> > _CON.con_id,
> Please make sure you get the quoting right regarding table
> names. PostgreSQL will fold _CON into _con unless quoted
> "_CON". So, it may be that you created the table with quotes
> ("_CON"). Now, in your query you don't use quotes and thusly
> it is looking for a _con table. The simple rule of thumb is
> to either always or never use quotes.
I don't think that this is the solution, becouse the query:
SELECT
_CON.con_id,
_MOD.mod_ty,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD,
db_data.mda_mod_con _CON,
db_data.mda_mak_eng _ENG,
db_data.set_mda_fue _FUE
WHERE
_MOD.mod_id = '283' AND
_CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND
_CON.ped > NOW() AND
_ENG.eng_id = _CON.eng_id AND
_ENG.eng_fue = _FUE.fue_id
...is working fine. I belive that this some problem with LEFT JOIN and
FROM statement. If I will rewrite this query:
SELECT
_CON.con_id,
_MOD.mod_ty,
_VER.version,
_YEA.year,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD
JOIN db_data.mda_mod_con _CON ON _CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND _CON.ped > NOW()
JOIN db_data.mda_mak_eng _ENG ON _ENG.eng_id = _CON.eng_id
JOIN db_data.set_mda_fue _FUE ON _ENG.eng_fue = _FUE.fue_id
LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
WHERE
_MOD.mod_id = '283'
... It also working fine. The question is, why my first query isn't
working:
SELECT
_CON.con_id,
_MOD.mod_ty,
_VER.version,
_YEA.year,
_CON.dri_id,
_CON.man_cod,
_ENG.eng_pow
FROM
db_data.mda_mod _MOD,
db_data.mda_mod_con _CON,
db_data.mda_mak_eng _ENG,
db_data.set_mda_fue _FUE
LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id
LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id
WHERE
_MOD.mod_id = '283' AND
_CON.mod_id = _MOD.mod_id AND
_CON.psd <= NOW() AND
_CON.ped > NOW() AND
_ENG.eng_id = _CON.eng_id AND
_ENG.eng_fue = _FUE.fue_id
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] SQL query problem
Hello, I'm in the middle of the migration process from MySQL to PostgreSQL and I cannot understand why this query isn't working (in MySQL it's working fine). PG returns: ERROR: Relation "_con" does not exist This is my query: SELECT _CON.con_id, _MOD.mod_ty, _VER.version, _YEA.year, _CON.dri_id, _CON.man_cod, _ENG.eng_pow FROM db_data.mda_mod _MOD, db_data.mda_mod_con _CON, db_data.mda_mak_eng _ENG, db_data.set_mda_fue _FUE LEFT JOIN db_data.mda_mod_ver _VER ON _VER.ver_id = _CON.ver_id LEFT JOIN db_data.mda_mod_yea _YEA ON _YEA.yea_id = _CON.yea_id WHERE _MOD.mod_id = '283' AND _CON.mod_id = _MOD.mod_id AND _CON.psd <= NOW() AND _CON.ped > NOW() AND _ENG.eng_id = _CON.eng_id AND _ENG.eng_fue = _FUE.fue_id I will be appreciated for you help. ML ---(end of broadcast)--- TIP 3: 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
[SQL] how to use COPY within plperl
Hello,
I need to use COPY (instead of INSERT) within plperl function. I know
that COPY will work if data will be taken from file - however I need to
use STDIN. I tried this:
spi_exec_query("COPY sometable (field1, field2) FROM
stdin;"."\n"."sometext"."\t"."sometext"."\n"."\.")
But it didn't work. Thanks in advance.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] how to use COPY within plperl
Goutam Paruchuri wrote: Why can you not use simple insert statements (sql insert). Copy is meant to transfer large amount of data from text files to databases and vice versa. "Insert" is much slower if there are many (hundreds, thousands) data to be inserted - and in my case there will be thousands of inserts made by plperl. I can make a file with the data (and load using COPY), but it would be better to use copy from stdin. Thanks ML ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Get current trasanction id
Hello, is there any way to get current transaction id using plpgsql or sql ? Thanks in advance for any help. ML ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [GENERAL] Get current trasanction id
Michael Fuhr napisaÅ(a): On Mon, Dec 27, 2004 at 09:52:57AM +0100, Marek Lewczuk wrote: is there any way to get current transaction id using plpgsql or sql ? A couple of people have posted suggestions but I'll ask a question: Why do you want the transaction ID? What problem are you trying to solve? Michael, I've already solved the problem - I found somewhere on the web a very simple C function, which returns transaction id. I need transaction id for plperl functions setVar, getVar (using $_SHARED array). Functions can write/read variables which are available either for connection or transaction. Regards, ML ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
