[
https://issues.apache.org/jira/browse/CALCITE-4728?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17398319#comment-17398319
]
James Starr commented on CALCITE-4728:
--------------------------------------
[~julianhyde], I see what you are saying about Oracles syntax requiring it to
be parsed and not simply treated as black box. Looking T-SQL, is seem to
follow similar pattern as PL/SQL. To run other languages on Sql Server, it
looks like the script is held in a string literal. I do not see a way to
inline other languages in Oracle. So Postgres way of handling multiple
languages as a generalized approach that includes PL/SQL seems to be the
exception not the rule. So a more generalized approach do not seem to
achievable. So, yes, I agree.
Example of running python on Sql Server:
{code:sql}
EXECUTE sp_execute_external_script
@language = N'Python',
@script=N'OutputDataSet = InputDataSet
for i in OutputDataSet["sal"]:
OutputDataSet["Bonus"]=OutputDataSet["sal"]*0.05',
@input_data_1 = N'SELECT [empno],[ename],sal,0 as Bonus from EMP'
WITH RESULT SETS ((EMPNO int, ENAME varchar(10), SAL float, Bonus float)
{code}
Example PL/pgSql:
{code:sql}
CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;
{code}
> Parse and validate procedural code (such as SQL/PSM, PL/SQL, PL/pgSQL, T-SQL)
> -----------------------------------------------------------------------------
>
> Key: CALCITE-4728
> URL: https://issues.apache.org/jira/browse/CALCITE-4728
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Priority: Major
>
> Parse and validate procedural code (such as the SQL standard's SQL/PSM,
> Oracle's PL/SQL, PostgreSQL's PL/pgSQL, MSSQL's T-SQL).
> This would entail:
> * Extensions to the SQL parser. (I'm not sure whether this would be the core
> parser or an extended parser such as Babel.)
> * AST classes (sub-classes of {{SqlNode}}) for functions, procedures,
> blocks, variable and parameter declarations, variable assignment,
> if-then-else, loop, and so forth.
> * Extensions to the validator to validate blocks.
> * Extensions to the validator to validate a SQL statement that is inside a
> block. (Variables and parameters are in scope.)
> Optional:
> * Extend Interpreter to execute functions, procedures, blocks.
> * Some means to convert a block into executable Java code.
> * Extend {{SqlDialect}} so that ASTs of functions, procedures and blocks can
> be emitted using the syntax of particular dialects.
> The languages are sufficiently similar that we can use the same AST classes
> for all.
> I don't think we need to extend {{RelNode}} to represent blocks. (It's not a
> good fit, since a block does not evaluate to a relation.) Possibly we would
> create some data structure to represent a validated block (e.g. the type of
> each variable; each use of a variable points to the variable's definition).
> {{SqlToRelConverter}} would create this data structure in order "freeze" the
> state of the validator.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
