001/*
002 * Copyright (c) 2004-2023 Tada AB and other contributors, as listed below.
003 *
004 * All rights reserved. This program and the accompanying materials
005 * are made available under the terms of the The BSD 3-Clause License
006 * which accompanies this distribution, and is available at
007 * http://opensource.org/licenses/BSD-3-Clause
008 *
009 * Contributors:
010 *   Tada AB
011 *   Chapman Flack
012 */
013package org.postgresql.pljava.example.annotation;
014
015import java.sql.Connection;
016import java.sql.DriverManager;
017import java.sql.PreparedStatement;
018import java.sql.ResultSet;
019import java.sql.SQLException;
020
021import org.postgresql.pljava.ResultSetHandle;
022import org.postgresql.pljava.annotation.Function;
023import org.postgresql.pljava.annotation.SQLAction;
024
025/**
026 * Example implementing the {@code ResultSetHandle} interface, to return
027 * the {@link ResultSet} from any SQL {@code SELECT} query passed as a string
028 * to the {@link #executeSelect executeSelect} function.
029 */
030@SQLAction(requires="selecttorecords fn",
031install=
032" SELECT " +
033"  CASE WHEN r IS DISTINCT FROM ROW('Foo'::varchar, 1::integer, 1.5::float, " +
034"       23.67::decimal(8,2), '2005-06-01'::date, '20:56'::time, " +
035"       '192.168'::cidr) " +
036"  THEN javatest.logmessage('WARNING', 'SetOfRecordTest not ok') " +
037"  ELSE javatest.logmessage('INFO', 'SetOfRecordTest ok') " +
038"  END " +
039" FROM " +
040"  javatest.executeselecttorecords( " +
041"   'select ''Foo'',  1,  1.5::float,  23.67,  ''2005-06-01'',  " +
042"           ''20:56''::time, ''192.168.0''') " +
043"  AS r(t_varchar varchar, t_integer integer, t_float float, " +
044"      t_decimal decimal(8,2), t_date date, t_time time, t_cidr cidr)"
045)
046public class SetOfRecordTest implements ResultSetHandle {
047
048    @Function(schema="javatest", name="executeselecttorecords",
049              provides="selecttorecords fn")
050    public static ResultSetHandle executeSelect(String selectSQL)
051            throws SQLException {
052        return new SetOfRecordTest(selectSQL);
053    }
054
055    private final PreparedStatement m_statement;
056
057    public SetOfRecordTest(String selectSQL) throws SQLException {
058        Connection conn = DriverManager
059                .getConnection("jdbc:default:connection");
060        m_statement = conn.prepareStatement(selectSQL);
061    }
062
063    @Override
064    public void close() throws SQLException {
065        m_statement.close();
066    }
067
068    @Override
069    public ResultSet getResultSet() throws SQLException {
070        return m_statement.executeQuery();
071    }
072}