Обсуждение: 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.