unable to call a function

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
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general