Thread: unable to call a function
i've write this function that search if inside a specified table there's a specified value: CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), table_column character(20) ) RETURNS BOOLEAN AS $$ BEGIN RETURN EXECUTE 'SELECT EXISTS(SELECT * FROM table_name WHERE table_column = id)'; END; $$ LANGUAGE plpgsql but when i try to call it i always receive an error and the function will not call. where is the problem? -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
You are passing the literal value "table_name" as the table, and "column_name" as the column.
--
You need to concatenate the substituted values onto the string with the || operator:
return execute 'select exists(select * from ' || quote_ident(table_name) || ' where ' || quote_ident(table_column) || ' = ' || id || ')';
(not tested)
On Thu, Jul 4, 2013 at 11:53 AM, giozh <giozh@yahoo.it> wrote:
i've write this function that search if inside a specified table there's a
specified value:
CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50),
table_column character(20) ) RETURNS BOOLEAN AS $$
BEGIN
RETURN EXECUTE 'SELECT EXISTS(SELECT * FROM table_name WHERE table_column =
id)';
END;
$$ LANGUAGE plpgsql
but when i try to call it i always receive an error and the function will
not call. where is the problem?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
moshe@neadwerx.com | www.neadwerx.com
moshe@neadwerx.com | www.neadwerx.com
"Quality is not an act, it is a habit." -- Aristotle
On 07/04/2013 08:53 AM, giozh wrote: > i've write this function that search if inside a specified table there's a > specified value: > > CREATE FUNCTION check_if_if_exist(id INTEGER, table_name character(50), > table_column character(20) ) RETURNS BOOLEAN AS $$ > > BEGIN > RETURN EXECUTE 'SELECT EXISTS(SELECT * FROM table_name WHERE table_column = > id)'; > END; > > $$ LANGUAGE plpgsql > > but when i try to call it i always receive an error and the function will > not call. where is the problem? Try: CREATE OR REPLACE FUNCTION utility.check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean LANGUAGE plpgsql AS $function$ DECLARE _exists boolean; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM '|| table_name || ' WHERE ' || table_column ||' = $1)' INTO _exists USING id ; RETURN _exists; END; More information here: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Adrian Klaver adrian.klaver@gmail.com
ok, i've modify mi function, but now i'm not able to execute it: SELECT check_if_exist(10, table, col); ERROR: column "table" does not exist -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762599.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
ok, i've modify mi function, but now i'm not able to execute it: SELECT check_if_exist(10, table, col); ERROR: column "table" does not exist -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762600.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/04/2013 09:33 AM, giozh wrote: > ok, i've modify mi function, but now i'm not able to execute it: > > SELECT check_if_exist(10, table, col); > > ERROR: column "table" does not exist > > test=> select check_if_if_exist(1, 'int_test', 'i'); check_if_if_exist ------------------- t (1 row) You need to quote table_name and table_column. -- Adrian Klaver adrian.klaver@gmail.com
something gone wrong the same... REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean AS $BODY$ DECLARE res BOOLEAN; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name|| 'WHERE'||table_column||'='||$1||')' INTO res USING id; RETURN res; END; select check_if_exist(10, 'prova', 'identificatore'); RROR: function check_if_exist(integer, unknown, unknown) does not exist LINE 1: select check_if_exist(10, 'prova', 'identificatore'); ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762605.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/04/2013 10:14 AM, giozh wrote: > something gone wrong the same... > > REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name > character, table_column character) > RETURNS boolean AS > $BODY$ > > DECLARE res BOOLEAN; > > BEGIN > EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name|| > 'WHERE'||table_column||'='||$1||')' INTO res USING id; > RETURN res; > END; > > select check_if_exist(10, 'prova', 'identificatore'); > > > RROR: function check_if_exist(integer, unknown, unknown) does not exist > LINE 1: select check_if_exist(10, 'prova', 'identificatore'); > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > Not sure if there is a cut and paste error involved but the function should be something like: CREATE OR REPLACE FUNCTION utility.check_if_if_exist(id integer, table_name character, table_column character) RETURNS boolean LANGUAGE plpgsql AS $BODY$ DECLARE res BOOLEAN; BEGIN EXECUTE 'SELECT EXISTS(SELECT * FROM '||table_name|| ' WHERE '||table_column||'='||$1||')' INTO res USING id; RETURN res; END; $BODY$; I also put in some spaces to make the query work. -- Adrian Klaver adrian.klaver@gmail.com
On 07/04/2013 10:14 AM, giozh wrote: > something gone wrong the same... > > REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name > character, table_column character) > RETURNS boolean AS > $BODY$ > > DECLARE res BOOLEAN; > > BEGIN > EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name|| > 'WHERE'||table_column||'='||$1||')' INTO res USING id; > RETURN res; > END; > > select check_if_exist(10, 'prova', 'identificatore'); > > > RROR: function check_if_exist(integer, unknown, unknown) does not exist > LINE 1: select check_if_exist(10, 'prova', 'identificatore'); > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > Got hung up on the function definition and skipped over the error message. Looks like Postgres is not seeing 'prova' and 'identificatore' as text. What happens if you do?: select check_if_exist(10, 'prova'::text, 'identificatore'::text); -- Adrian Klaver adrian.klaver@gmail.com
Hello 2013/7/4 Adrian Klaver <adrian.klaver@gmail.com>: > On 07/04/2013 10:14 AM, giozh wrote: >> >> something gone wrong the same... >> >> REATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name >> character, table_column character) >> RETURNS boolean AS >> $BODY$ >> >> DECLARE res BOOLEAN; >> >> BEGIN >> EXECUTE 'SELECT EXISTS(SELECT * FROM'||table_name|| >> 'WHERE'||table_column||'='||$1||')' INTO res USING id; >> RETURN res; >> END; >> >> select check_if_exist(10, 'prova', 'identificatore'); >> >> >> RROR: function check_if_exist(integer, unknown, unknown) does not exist >> LINE 1: select check_if_exist(10, 'prova', 'identificatore'); >> ^ >> HINT: No function matches the given name and argument types. You might >> need >> to add explicit type casts. >> > > Got hung up on the function definition and skipped over the error message. > Looks like Postgres is not seeing 'prova' and 'identificatore' as text. > > What happens if you do?: > > > select check_if_exist(10, 'prova'::text, 'identificatore'::text); > there is wrong datatype CREATE OR REPLACE FUNCTION check_if_if_exist(id integer, table_name character, table_column character) no "character" - use instead "text" or "varchar" Regards Pavel > -- > Adrian Klaver > adrian.klaver@gmail.com > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
so thanks to all for the answers. But i'm going to be frustrated, no one of your solutions seems to work, and i can't understand why, because i've write another two functions that works well... I always obtain error: or unknown function (if i pass args without ' ') or "column not exist". i've noticed that on my postgres server, there's two database: one i've created for my scope, and another named postgres (i think created by default). Maybe should i specify on wich database my function should work (also if function compare only inside my personal database)? -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762649.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/05/2013 12:29 AM, giozh wrote: > so thanks to all for the answers. But i'm going to be frustrated, no one of > your solutions seems to work, and i can't understand why, because i've write > another two functions that works well... > I always obtain error: or unknown function (if i pass args without ' ') or > "column not exist". So does it work if you quote the table_name and table_column names? Have you changed the data type to varchar? > i've noticed that on my postgres server, there's two database: one i've > created for my scope, and another named postgres (i think created by > default). Maybe should i specify on wich database my function should work > (also if function compare only inside my personal database)? Functions are created per database, To be more precise per schema in a database. Functions can be overloaded, so it is possible there is more than one in your database. To check do the following from the psql propmpt: \df check_if_if_exist > -- Adrian Klaver adrian.klaver@gmail.com
ok, now it works with varchar args. thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/unable-to-call-a-function-tp5762590p5762891.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.