Need to insert the database table

Question

I got the error while giving the input, so avoid the error and store the postgre database table_sp table

        package mypkg;

        import java.sql.CallableStatement;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.ResultSet;
        import java.sql.SQLException;
        import java.sql.Statement;
        import java.util.Scanner;

        public class PostgreSp {

            public static void main(String args[]) throws SQLException, ClassNotFoundException {
                Connection cn;
                Statement st;
                ResultSet rs;

                try {
                    Scanner in = new Scanner(System.in);

                    System.out.println("Enter a PK");
                    String pk = in.nextLine();
                    System.out.println("Enter a user");
                    String u = in.nextLine();
                    System.out.println("Enter a pass");
                    String ps = in.nextLine();
                    Class.forName("org.postgresql.Driver");
                    cn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/samples", "postgres", "analuo");

                    CallableStatement calstat = cn.prepareCall("{call new(?,?,?)}");
                    calstat.setString(1, pk);
                    calstat.setString(2, u);
                    calstat.setString(3, ps);
                    int i = calstat.executeUpdate();
                    cn.close();
                    calstat.close();
                    System.out.println("Your data has been inserted into table.");

                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }

STORE PROCEDURE:

   CREATE OR REPLACE FUNCTION new(pk character varying, u character varying,ps character varying)
      RETURNS SETOF record AS
    $BODY$
    BEGIN

    RAISE NOTICE 'PK is %','--'pk;
     EXECUTE 'INSERT INTO table_sp("pk_id","username","password")VALUES ('''||pk||''' ,'''||u||''','''||ps||''')' ;
    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100
      ROWS 1000;
    ALTER FUNCTION new(character varying, character varying,character varying)
      OWNER TO postgres;

SAMPLE OUTPUT:

run:
Enter a PK
1
Enter a user
admin
Enter a pass
admin
org.postgresql.util.PSQLException: ERROR: a column definition list is required for functions returning "record"
  Position: 15
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:330)
    at mypkg.PostgreSp.main(PostgreSp.java:37)
BUILD SUCCESSFUL (total time: 36 seconds)

I am attaching store procedure also. How to avoid the error and store the table.

When I search in Google for 'ERROR: a column definition list is required for functions returning ' it comes up with this as the first answer: http://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret - Google is a great place to start looking for answers — Dave Richardson, Jul 17 '13 at 07:59

score 1 · Answer 1 · answered Jul 17 '13 at 10:22

You've defined the function with RETURNS SETOF record but the function body doesn't return anything. Looking at your java code, you're not expecting it to return anything either. (using executeUpdate)

Change the function to RETURNS VOID so that Postgres knows nothing is to be returned.

Need to insert the database table

1 Answers1