Quantcast

unable to call a function

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

unable to call a function

giozh
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?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Moshe Jacobson
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 <[hidden email]> 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 ([hidden email])
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
[hidden email] | 
www.neadwerx.com

"Quality is not an act, it is a habit." -- Aristotle
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Adrian Klaver-3
In reply to this post by giozh
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

giozh
In reply to this post by giozh
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

giozh
In reply to this post by Moshe Jacobson
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Adrian Klaver-3
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

giozh
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.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Adrian Klaver-3
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Adrian Klaver-3
In reply to this post by giozh
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Pavel Stehule
Hello

2013/7/4 Adrian Klaver <[hidden email]>:

> 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
> [hidden email]
>
>
>
> --
> Sent via pgsql-general mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

giozh
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)?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

Adrian Klaver-3
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
[hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: unable to call a function

giozh
ok, now it works with varchar args. thanks
Loading...