5

I'm using PostgreSQL 9.2.4.

postgres=# select version();

                           version
-------------------------------------------------------------
 PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
(1 row)

sqlfiddle link

My Query executes the insertion safely. What i need is that my function should return something except the void datatype. Something like text("inserted into table") or integer(0-false,1-true) , it will be useful for me to validate whether it is inserted or not?

I need a syntax for a function that returns an integer or a text when an insertion is done. For validation purpose. Is there any way to solve this?

Improve this question
5
  • returns integer instead of returns void? Commented Jul 11, 2013 at 11:10
  • Have you tried RETURNING clause for INSERT query? Details here. Commented Jul 11, 2013 at 11:21
  • 1
    Which parts of the Postgres' documentation about functions on PL/pgSQL are not clear or helpful? Commented Jul 11, 2013 at 13:06
  • What client driver are you using? Java + PgJDBC? C# + nPgSQL? Python + psycopg2? What? You need to do this on the client side. Commented Jul 12, 2013 at 0:40
  • java + pgJDBC is my driver Commented Jul 12, 2013 at 6:30

3 Answers 3

Reset to default
5

What you probably need

Most likely you need one function to return text and another one to return integer or a function that returns boolean to indicate success. All of this is trivial and I'll refer you to the excellent manual on CREATE FUNCTION or code examples in similar questions on SO.

What you actually asked

How to write a function that returns text or integer values?

... in the sense that we have one return type being either text or integer. Not as trivial, but also not impossible as has been suggested. The key word is: polymorphic types.

Building on this simple table:

CREATE TABLE tbl(
  tbl_id int,
  txt    text,
  nr     int
);

This function returns either integer or text (or any other type if you allow it), depending on the input type.

CREATE FUNCTION f_insert_data(_id int, _data anyelement, OUT _result anyelement)
  RETURNS anyelement AS
$func$
BEGIN

CASE pg_typeof(_data) 
WHEN 'text'::regtype THEN
    INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data)
    RETURNING txt
    INTO _result;

WHEN 'integer'::regtype THEN
    INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data)
    RETURNING nr
    INTO _result;

ELSE
    RAISE EXCEPTION 'Unexpected data type: %', pg_typeof(_data)::text;
END CASE;

END
$func$
LANGUAGE plpgsql;

Call:

SELECT f_insert_data(1, 'foo'::text);  -- explicit cast needed.
SELECT f_insert_data(1, 7);

Simple case

One function that returns TRUE / FALSE to indicate whether a row has been inserted, only one input parameter of varying type:

CREATE FUNCTION f_insert_data2(_id int, _data anyelement)
  RETURNS boolean AS
$func$
BEGIN

CASE pg_typeof(_data)
WHEN 'text'::regtype THEN
   INSERT INTO tbl(tbl_id, txt) VALUES(_id, _data);

WHEN 'integer'::regtype THEN
   INSERT INTO tbl(tbl_id, nr) VALUES(_id, _data);

ELSE
   RAISE EXCEPTION 'Unexpected data type: >>%<<', pg_typeof(_data)::text;
END CASE;

IF FOUND THEN RETURN TRUE;
ELSE RETURN FALSE;
END IF;

END
$func$
LANGUAGE plpgsql;

The input type can be replaced with a text parameter for most purposes, which can be cast to and from any other type.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Is it just me, or is the whole idea behind this really weird? Just using the affected-row-count when doing the insert standalone should be fine, and otherwise there's GET DIAGNOSTICS, FOUND and INSERT ... RETURNING.
@CraigRinger: Must be just you. :p Seriously, I don't think the OP needs any of this, as already hinted every so gently. It's advanced stuff for special purposes.
Yeah. I've added an example of how to do it the sane way, using Python since the tools/language used by the OP aren't specified. I suspect they're in a place that's drunk the "stored procedures for everything" kool-aid without actually understanding it, though...
3

It sounds like you're solving a problem by creating a bigger problem.

You don't need a function for this at all. Do it on the client side by checking the affected rows count that's returned by every DML query, or use INSERT ... RETURNING.

You didn't mention your client language, so here's how to do it in Python with psycopg2. The same approach applies in other languages with syntax variations.

#!/usr/bin/env python
import psycopg2

# Connect to the db
conn = psycopg2.connect("dbname=regress")
curs = conn.cursor()

# Set up the table to use
curs.execute("""
DROP TABLE IF EXISTS so17587735;

CREATE TABLE so17587735 (
    id serial primary key,
    blah text not null
);
""");

# Approach 1: Do the insert and check the rowcount:
curs.execute("""
INSERT INTO so17587735(blah) VALUES ('whatever');
""");
if curs.rowcount != 1:
    raise Exception("Argh, insert affected zero rows, wtf?")
print("Inserted {0} rows as expected".format(curs.rowcount))

# Approach 2: Use RETURNING
curs.execute("""
INSERT INTO so17587735(blah) VALUES ('bored') RETURNING id;
""");
returned_rows = curs.fetchall();
if len(returned_rows) != 1:
    raise Exception("Got unexpected row count {0} from INSERT".format(len(returned_rows)))

print("Inserted row id is {0}".format(returned_rows[0][0]))

In the case of PL/PgSQL calling INSERT you can use the GET DIAGNOSTICS command, the FOUND variable, or RETURN QUERY EXECUTE INSERT ... RETURNING .... Using GET DIAGNOSTICS:

CREATE OR REPLACE FUNCTION blah() RETURNS void AS $$
DECLARE
    inserted_rows integer;
BEGIN
    INSERT INTO some_table VALUES ('whatever');
    GET DIAGNOSTICS inserted_rows = ROW_COUNT;
    IF inserted_rows <> 1 THEN
        RAISE EXCEPTION 'Failed to insert rows; expected 1 row, got %', inserted_rows;
    END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;

or if you must return values and must for some reason use PL/PgSQL:

CREATE OR REPLACE FUNCTION blah() RETURNS SETOF integer AS $$
BEGIN
    RETURN QUERY EXECUTE INSERT INTO some_table VALUES ('whatever') RETURNING id;
END;
$$ LANGUAGE plpgsql VOLATILE;

(assuming the key is id)

which would be the same as:

CREATE OR REPLACE FUNCTION blah() RETURNS SETOF integer AS $$
INSERT INTO some_table VALUES ('whatever') RETURNING id;
$$ LANGUAGE sql;

or just

INSERT INTO some_table VALUES ('whatever') RETURNING id;

In other words: Why wrap this in a function? It doesn't make sense. Just check the row-count client side, either with RETURNING or by using the client driver's affected-rows count for INSERT.

Improve this answer

3 Comments

actually the scenario is that i have a back-end code using functions as i kept the SQL FIDDLE of it! when i am connecting it with java frontend i need a response from the function i.e.., a return value so that i can make sure that insertion into database is been done..so i want to clarify whether is there any chance of returning a value to the function which is been called. or is there any other way to solve this? @craig rainger
@user2561626 Sure. Check GET DIAGNOSTICS in the procedure, and RAISE EXCEPTION if it reports no rows.
@user2561626 Answer rewritten.
0

A function can only return one type. In your case, you could create a composite type with two fields, one integer and one text, and return that.

Improve this answer

1 Comment

OK, partially. Polymorphic return types require a polymorphic argument, which isn't the case here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.