osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Creating a parametric SqlFunction


In SQL, a double-quoted string is not a character literal but an identifier. I think you should use single-quotes.

> On Nov 12, 2018, at 12:32 PM, Pearu Peterson <pearu.peterson@xxxxxxxxxxxxx> wrote:
> 
> Hi!
> 
> My goal is to achieve something like this:
> 
> SELECT foo("some text", some_column) FROM some_table
> 
> by defining a new SQL operator foo that first argument should be a string
> parameter and the second is a column from a given table.
> 
> For that, I have defined
> 
> public static class foo extends SqlFunction {
>    foo() {
>      super("FOO",
>     SqlKind.OTHER_FUNCTION,
>             null,
>             null,
>     OperandTypes.family(SqlTypeFamily.CHARACTER, SqlTypeFamily.NUMERIC),
>             SqlFunctionCategory.SYSTEM);
>   }
>   @Override
>   public RelDataType inferReturnType(SqlOperatorBinding opBinding) {
>      assert opBinding.getOperandCount() == 2;
>      final RelDataTypeFactory typeFactory
>             = opBinding.getTypeFactory();
>      return typeFactory.createSqlType(SqlTypeName.DOUBLE);
>    }
>  }
> 
> but when running the query I get an exception:
> 
> Column 'some text' not found in any table
> 
> What I am doing wrong?
> How one can define a SQL operator so that not all of its arguments do not
> have to be columns?
> Are there alternative ways to approach the goal?
> 
> Best regards,
> Pearu