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 => 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 => ('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}