Thread: Function and insert

Hi,

I want to do a function that insert a row on a table like :

CREATE FUNCTION ajout_secu(INT4) RETURNS OID    AS 'INSERT INTO test_2 VALUES($1);'    LANGUAGE 'SQL';

PG refuse to accept the type returns oid as the function is not a SELECT.
What can I do ?

thanks for your help,

LP

I'm using 7.2.1, trying to run a query like this:

DELETE FROM table WHERE col1='something' AND col2 IN
('aasdoijhfoisdfsdoif','sdfsdfsdfsadfsdf', ... );

In the parantheses I have 6400 names, each about 20 characters. I'm
using libpq from C. This did not work very well, but the result was
very unexpected.

My application has several threads, each opening its own connection to
the database. The above query was run in a transaction followed by a
COMMIT. There was no error from running the above query, but instead,
it seems that the query was never run at all. As a side effect, every
other connection to the database always got:

NOTICE:  current transaction is aborted, queries ignored until end of
transaction block

when trying to run a query. I thought that the transactions in
different connections didn't have anything to do with each other.


If I limited the number of names in the failing query to 3200, it
worked well and as expected.


Is there a limit in libpq of the length of a query? And if this is
exceeded, shouldn't PQexec() give an error?


Greetings,

Tomas

On Wednesday 27 Nov 2002 11:05 am, Laurent Patureau wrote:
> Hi,
>
> I want to do a function that insert a row on a table like :
>
> CREATE FUNCTION ajout_secu(INT4) RETURNS OID
>      AS 'INSERT INTO test_2 VALUES($1);'
>      LANGUAGE 'SQL';
>
> PG refuse to accept the type returns oid as the function is not a SELECT.
> What can I do ?

You're not returning anything - try something like:

CREATE FUNCTION foo_ins(int4) RETURNS int4 AS
'INSERT INTO foo VALUES($1); SELECT $1;'
LANGUAGE 'SQL';

--  Richard Huxton