001/*
002 * Copyright (c) 2018-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 *   Chapman Flack
011 */
012package org.postgresql.pljava.example.annotation;
013
014import java.sql.ResultSet;
015import java.sql.ResultSetMetaData;
016import java.sql.SQLException;
017
018import static java.util.Arrays.fill;
019
020import org.postgresql.pljava.ResultSetProvider;
021import org.postgresql.pljava.annotation.Function;
022import org.postgresql.pljava.annotation.SQLType;
023
024/**
025 * Example demonstrating the use of a {@code RECORD} parameter as a way to
026 * supply an arbitrary sequence of named, typed parameters to a PL/Java
027 * function.
028 *<p>
029 * Also tests the proper DDR generation of defaults for such parameters.
030 */
031public class RecordParameterDefaults implements ResultSetProvider
032{
033    /**
034     * Return the names, types, and values of parameters supplied as a single
035     * anonymous RECORD type; the parameter is given an empty-record default,
036     * allowing it to be omitted in calls, or used with the named-parameter
037     * call syntax.
038     *<p>
039     * For example, this function could be called as:
040     *<pre>
041     * SELECT (paramDefaultsRecord()).*;
042     *</pre>
043     * or as:
044     *<pre>
045     * SELECT (paramDefaultsRecord(params =&gt; s)).*
046     * FROM (SELECT 42 AS a, '42' AS b, 42.0 AS c) AS s;
047     *</pre>
048     */
049    @Function(
050        schema = "javatest",
051        out = {
052            "name text", "pgtypename text", "javaclass text", "tostring text"
053        }
054    )
055    public static ResultSetProvider paramDefaultsRecord(
056        @SQLType(defaultValue={})ResultSet params)
057    throws SQLException
058    {
059        return new RecordParameterDefaults(params);
060    }
061
062    /**
063     * Like paramDefaultsRecord but illustrating the use of a named row type
064     * with known structure, and supplying a default for the function
065     * parameter.
066     *<p>
067     *<pre>
068     * SELECT paramDefaultsNamedRow();
069     *
070     * SELECT paramDefaultsNamedRow(userWithNum =&gt; ('fred', 3.14));
071     *</pre>
072     */
073    @Function(
074        requires = "foobar tables", // created in Triggers.java
075        schema = "javatest"
076        )
077    public static String paramDefaultsNamedRow(
078        @SQLType(value="javatest.foobar_2", defaultValue={"bob", "42"})
079        ResultSet userWithNum)
080    throws SQLException
081    {
082        return String.format("username is %s and value is %s",
083            userWithNum.getObject("username"), userWithNum.getObject("value"));
084    }
085
086
087
088    private final ResultSetMetaData m_paramrsmd;
089    private final Object[] m_values;
090    
091    RecordParameterDefaults(ResultSet paramrs) throws SQLException
092    {
093        m_paramrsmd = paramrs.getMetaData();
094        /*
095         * Grab the values from the parameter SingleRowResultSet now; it isn't
096         * guaranteed to stay valid for the life of the set-returning function.
097         */
098        m_values = new Object [ m_paramrsmd.getColumnCount() ];
099        for ( int i = 0; i < m_values.length; ++ i )
100            m_values[i] = paramrs.getObject( 1 + i);
101    }
102
103    @Override
104    public boolean assignRowValues(ResultSet receiver, int currentRow)
105    throws SQLException
106    {
107        int col = 1 + currentRow;
108        if ( col > m_paramrsmd.getColumnCount() )
109            return false;
110        receiver.updateString("name", m_paramrsmd.getColumnLabel(col));
111        receiver.updateString("pgtypename", m_paramrsmd.getColumnTypeName(col));
112        Object o = m_values[col - 1];
113        receiver.updateString("javaclass", o.getClass().getName());
114        receiver.updateString("tostring", o.toString());
115        return true;
116    }
117
118    @Override
119    public void close() throws SQLException
120    {
121        fill(m_values, null);
122    }
123}