/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 *
 */
// https://issues.jboss.org/browse/TEIID-2477
// https://issues.jboss.org/browse/TEIID-2344
// https://issues.jboss.org/browse/TEIID-3366
// https://issues.jboss.org/browse/TEIID-3381
// https://issues.jboss.org/browse/TEIID-3704
// https://issues.jboss.org/browse/TEIID-2453
// https://issues.jboss.org/browse/TEIID-4008
// https://issues.jboss.org/browse/TEIID-3888 (partially, without Redshift translator)
// https://issues.jboss.org/browse/TEIID-4893 (partially, without Redshift translator)
// https://issues.jboss.org/browse/TEIID-3046
// https://issues.jboss.org/browse/TEIID-5051
// https://issues.jboss.org/browse/TEIID-5161
// https://issues.redhat.com/browse/TEIID-6043
// https://issues.redhat.com/browse/TEIID-4202
// https://issues.redhat.com/browse/TEIID-3307
// https://issues.redhat.com/browse/TEIID-2384
// https://issues.redhat.com/browse/TEIID-2348
// https://issues.redhat.com/browse/TEIID-4393
// https://issues.redhat.com/browse/TEIID-5227
// https://issues.redhat.com/browse/TEIID-3869
// https://issues.redhat.com/browse/TEIID-5480
// https://issues.redhat.com/browse/TEIID-3302
// https://issues.redhat.com/browse/TEIID-5499
// https://issues.redhat.com/browse/TEIID-4137
package org.teiid.translator.jdbc.postgresql;

import com.datavirtuality.dv.core.interfaces.DVCaller;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import org.teiid.GeometryInputSource;
import org.teiid.core.types.ArrayImpl;
import org.teiid.core.types.BinaryType;
import org.teiid.core.types.ClobImpl;
import org.teiid.core.types.ClobType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.types.JsonType;
import org.teiid.language.*;
import org.teiid.language.Like.MatchMode;
import org.teiid.language.SQLConstants.NonReserved;
import org.teiid.logging.LogConstants;
import org.teiid.logging.LogManager;
import org.teiid.metadata.Column;
import org.teiid.metadata.RuntimeMetadata;
import org.teiid.translator.*;
import org.teiid.translator.jdbc.*;
import org.teiid.translator.jdbc.JDBCMetadataProcessor.ITypeConverter;
import org.teiid.translator.jdbc.oracle.LeftOrRightFunctionModifier;
import org.teiid.translator.jdbc.oracle.MonthOrDayNameFunctionModifier;
import org.teiid.translator.jdbc.oracle.OracleFormatFunctionModifier;

import java.io.IOException;
import java.sql.*;
import java.sql.Array;
import java.time.LocalTime;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import static org.teiid.translator.jdbc.ReplaceNullCharsIntf.Util.replaceNullCharsIfNeeded;


/**
 * Translator class for PostgreSQL.  Updated to expect a 8.0+ jdbc client
 * @since 4.3
 */
@Translator(name="postgresql", description="A translator for postgreSQL Database")
public class PostgreSQLExecutionFactory extends JDBCExecutionFactory implements ReplaceNullCharsIntf {
	private static final String UUID = "uuid";
	private static final String JSON = "json";
	private static final String JSONB = "jsonb";

	private static final String UUID_TYPE = "uuid"; //$NON-NLS-1$
	private static final String INTEGER_TYPE = "integer"; //$NON-NLS-1$
    private static final String CITEXT_TYPE = "citext"; //$NON-NLS-1$

    public static final Version SEVEN_4 = Version.getVersion("7.4"); //$NON-NLS-1$
	public static final Version EIGHT_0 = Version.getVersion("8.0"); //$NON-NLS-1$
    public static final Version EIGHT_1 = Version.getVersion("8.1"); //$NON-NLS-1$
    public static final Version EIGHT_2 = Version.getVersion("8.2"); //$NON-NLS-1$
    public static final Version EIGHT_3 = Version.getVersion("8.3"); //$NON-NLS-1$
    public static final Version EIGHT_4 = Version.getVersion("8.4"); //$NON-NLS-1$
    public static final Version NINE_0 = Version.getVersion("9.0"); //$NON-NLS-1$
	public static final Version NINE_3 = Version.getVersion("9.3"); //$NON-NLS-1$

	//postgis versions
	public static final Version ONE_3 = Version.getVersion("1.3"); //$NON-NLS-1$
	public static final Version ONE_4 = Version.getVersion("1.4"); //$NON-NLS-1$
	public static final Version ONE_5 = Version.getVersion("1.5"); //$NON-NLS-1$
	public static final Version TWO_0 = Version.getVersion("2.0"); //$NON-NLS-1$

	private Version postGisVersion = Version.DEFAULT_VERSION;
	private boolean projSupported = false;

    private static final String TSVECTOR = "TSVECTOR";
    private static final String TSQUERY = "TSQUERY";
    public static final String GEOMETRY = "geometry";

	public PostgreSQLExecutionFactory() {
		setMaxDependentInPredicates(1);
		setMaxInCriteriaSize(Short.MAX_VALUE - 50); //set a value that is safely smaller than the max in case there are other parameters
		setSupportsCreateTableAsSelect(true);
		setSupportsMd5Function(true);
		setSupportsNativeQueries(true);
		setSupportsOrderByAlias(true);
        setMaxStringSize(1048000);
		// enable CTE
		setSupportsCommonTableExpressions(true);
		setSupportsRecursiveCommonTableExpressions(true);
	}

    public void start() throws TranslatorException {
        //TODO: all of the functions (except for convert) can be handled through just the escape syntax
        super.start();

        registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("ln")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LOG10, new AliasModifier("log")); //$NON-NLS-1$

        registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("&")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("~")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("|")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.BITXOR, new AliasModifier("#")); //$NON-NLS-1$

        registerFunctionModifier(SourceSystemFunctions.CHAR, new AliasModifier("chr")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.CONCAT, new AliasModifier("||")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("lower")); //$NON-NLS-1$
        // EXTENSION
     	// ISSUE: DVCORE-1034
     	// FIXED BY: Salvatore Raunich
     	// DATE: 2014.03.13
//        registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory()));
//        registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory()));
        FunctionModifier leftOrRightFunctionModifier = (FunctionModifier) DVCaller.call(53, getLanguageFactory());
        if (leftOrRightFunctionModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.LEFT, leftOrRightFunctionModifier);
        	registerFunctionModifier(SourceSystemFunctions.RIGHT, leftOrRightFunctionModifier);
        } else {
        	// if the extension is not found, add the original function modifiers, so that TEIID tests do no fail.
          registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory()));
          registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory()));
        }
   		// END EXTENSION
	    registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new FunctionModifier() {

			@Override
		    public List<?> translate(Function function) {
				List<Object> parts = new ArrayList<Object>();
			    parts.add("substring("); //$NON-NLS-1$
			    parts.add(function.getParameters().get(0));
			    parts.add(" from "); //$NON-NLS-1$
				Expression index = function.getParameters().get(1);
				if (!(index instanceof Literal)) {
					parts.add("case sign("); //$NON-NLS-1$
					parts.add(index);
					parts.add(") when -1 then cast(null as int4) when 0 then 1 else "); //$NON-NLS-1$
					parts.add(index);
					parts.add(" end"); //$NON-NLS-1$
				} else {
					parts.add(index);
				}
			    if (function.getParameters().size() > 2) {
				    parts.add(" for "); //$NON-NLS-1$
				    parts.add(function.getParameters().get(2));
				}
			    parts.add(")"); //$NON-NLS-1$
			    return parts;
			}
		});

        registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("upper")); //$NON-NLS-1$

        registerFunctionModifier(SourceSystemFunctions.DAYNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Day"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.DAYOFWEEK, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.DAYOFYEAR, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.MONTHNAME, new MonthOrDayNameFunctionModifier(getLanguageFactory(), "Month"));//$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.QUARTER, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.WEEK, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractFunctionModifier(INTEGER_TYPE));
        registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory()));
        registerFunctionModifier(SourceSystemFunctions.IFNULL, new AliasModifier("coalesce")); //$NON-NLS-1$

        // EXTENSION
        // ISSUE: DVCORE-2789
        // FIXED BY: Salvatore Raunich
        // DATE: 2015.04.20
//        registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new OracleFormatFunctionModifier("TO_TIMESTAMP(")); //$NON-NLS-1$
        FunctionModifier parseTimestampFunctionModifier = (FunctionModifier) DVCaller.call(72);
        if (parseTimestampFunctionModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, parseTimestampFunctionModifier);
        } else {
        	// if the extension is not found, add the original function modifiers, so that TEIID tests do no fail.
            registerFunctionModifier(SourceSystemFunctions.PARSETIMESTAMP, new PostgresFormatFunctionModifier("TO_TIMESTAMP(")); //$NON-NLS-1$
        }
        // END EXTENSION

        registerFunctionModifier(SourceSystemFunctions.FORMATTIMESTAMP, new PostgresFormatFunctionModifier("TO_CHAR(")); //$NON-NLS-1$

        registerFunctionModifier(SourceSystemFunctions.MOD, new ModFunctionModifier("%", getLanguageFactory(), Arrays.asList(TypeFacility.RUNTIME_TYPES.BIG_INTEGER, TypeFacility.RUNTIME_TYPES.BIG_DECIMAL))); //$NON-NLS-1$

        //specific to 8.2 client or later
        FunctionModifier timestampAddModifier = (FunctionModifier) DVCaller.call(133);
        if (timestampAddModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.TIMESTAMPADD, timestampAddModifier);
        }
	    registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$
        registerFunctionModifier(SourceSystemFunctions.SPLIT_PART, new AliasModifier("split_part")); //$NON-NLS-1$

        // EXTENSION
		// ISSUE: DV-106
		// FIXED BY: Salvatore Raunich
		// DATE: 2013.12.17
//        registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, new EscapeSyntaxModifier());
        FunctionModifier timestampDiffModifier = (FunctionModifier) DVCaller.call(37);
        if (timestampDiffModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.TIMESTAMPDIFF, timestampDiffModifier);
        }
		// END EXTENSION
        registerFunctionModifier(SourceSystemFunctions.TIMESTAMPCREATE, new FunctionModifier() {

            @Override
            public List<?> translate(Function function) {
                return Arrays.asList(function.getParameters().get(0), '+', function.getParameters().get(1));
            }
        });

        FunctionModifier curdateModifier = (FunctionModifier) DVCaller.call(130);
        if (curdateModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.CURDATE, curdateModifier);
        }

        registerFunctionModifier(SourceSystemFunctions.ARRAY_GET, new FunctionModifier() {

			@Override
			public List<?> translate(Function function) {
				return Arrays.asList(function.getParameters().get(0), '[', function.getParameters().get(1), ']');
			}
		});
        registerFunctionModifier(SourceSystemFunctions.ARRAY_LENGTH, new FunctionModifier() {

			@Override
			public List<?> translate(Function function) {
				if (function.getParameters().size() == 1) {
					function.getParameters().add(new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER));
				}
				return null;
			}
		});

        registerFunctionModifier(SourceSystemFunctions.ROUND, new FunctionModifier() {

            @Override
            public List<?> translate(Function function) {
                    if (function.getParameters().size() > 1) {
                            Expression ex = function.getParameters().get(0);
                            if (ex.getType() == TypeFacility.RUNTIME_TYPES.DOUBLE || ex.getType() == TypeFacility.RUNTIME_TYPES.FLOAT) {
                                    if (function.getParameters().get(1) instanceof Literal && Integer.valueOf(0).equals(((Literal)function.getParameters().get(1)).getValue())) {
                                            function.getParameters().remove(1);
                                    } else {
                                            function.getParameters().set(0, new Function(SourceSystemFunctions.CONVERT, Arrays.asList(ex, new Literal("bigdecimal", TypeFacility.RUNTIME_TYPES.STRING)), TypeFacility.RUNTIME_TYPES.BIG_DECIMAL)); //$NON-NLS-1$
                                    }
                            }
                    }
                    return null;
            }
        });

        //add in type conversion
        ConvertModifier convertModifier = new ConvertModifier();
        convertModifier.addTypeMapping("boolean", FunctionModifier.BOOLEAN); //$NON-NLS-1$
    	convertModifier.addTypeMapping("smallint", FunctionModifier.BYTE, FunctionModifier.SHORT); //$NON-NLS-1$
    	convertModifier.addTypeMapping(INTEGER_TYPE, FunctionModifier.INTEGER);
    	convertModifier.addTypeMapping("bigint", FunctionModifier.LONG); //$NON-NLS-1$
    	convertModifier.addTypeMapping("real", FunctionModifier.FLOAT); //$NON-NLS-1$
    	convertModifier.addTypeMapping("float8", FunctionModifier.DOUBLE); //$NON-NLS-1$
    	convertModifier.addTypeMapping("numeric(38)", FunctionModifier.BIGINTEGER); //$NON-NLS-1$
    	convertModifier.addTypeMapping("decimal", FunctionModifier.BIGDECIMAL); //$NON-NLS-1$
    	convertModifier.addTypeMapping("char(1)", FunctionModifier.CHAR); //$NON-NLS-1$
    	convertModifier.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$
    	convertModifier.addTypeMapping("date", FunctionModifier.DATE); //$NON-NLS-1$
    	convertModifier.addTypeMapping("time", FunctionModifier.TIME); //$NON-NLS-1$
    	convertModifier.addTypeMapping("timestamp", FunctionModifier.TIMESTAMP); //$NON-NLS-1$
	    convertModifier.addTypeMapping("geography", FunctionModifier.GEOGRAPHY); //$NON-NLS-1$
	    convertModifier.addTypeMapping("geometry", FunctionModifier.GEOMETRY); //$NON-NLS-1$
	    convertModifier.addTypeMapping("json", FunctionModifier.JSON); //$NON-NLS-1$
    	convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.TIMESTAMP, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				Expression expression = function.getParameters().get(0);
				List<Object> timestamp = Arrays.asList(
						"TO_TIMESTAMP('1970-01-01 '",
						" || ",
						expression,
						", 'yyyy-MM-dd HH24:mi:ss')");
				List<Object> result = new ArrayList<>(Arrays.asList(
						"CASE WHEN LOWER(PG_TYPEOF(",
						expression,
						")::text) = 'time without time zone' THEN "));
				result.addAll(timestamp);
				result.add(" ELSE ");
				result.addAll(timestamp);
				int databaseCalendarOffset = getDatabaseCalendar().getTimeZone().getOffset(0) / 1000;
				// Take the time zone offset from the timetz column in format 'HH24:mi' and subtract the offset from
				// the database calendar at the epoch start from it (thus effectively creating the corresponding UTC timestamp
				// without time zone; https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT),
				// and finally convert it to a UTC timestamp with time zone
				result.addAll(Arrays.asList(
						" AT TIME ZONE (((EXTRACT(TIMEZONE FROM ", expression, String.format(")::integer - %d) / 3600)", databaseCalendarOffset),
						" || ':' || ",
						"((EXTRACT(TIMEZONE FROM ", expression, String.format(")::integer - %d) %% 3600 / 60)) ", databaseCalendarOffset),
						"AT TIME ZONE 'UTC'"
				));
				result.add(" END");
				return result;
			}
		});
    	convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				return Arrays.asList("cast(date_trunc('second', ", function.getParameters().get(0), ") AS time)"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		});
    	convertModifier.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "YYYY-MM-DD")); //$NON-NLS-1$ //$NON-NLS-2$
    	convertModifier.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "HH24:MI:SS")); //$NON-NLS-1$ //$NON-NLS-2$
    	convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new ConvertModifier.FormatModifier("to_char", "YYYY-MM-DD HH24:MI:SS.US")); //$NON-NLS-1$ //$NON-NLS-2$
    	convertModifier.addConvert(FunctionModifier.BOOLEAN, FunctionModifier.STRING, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				Expression stringValue = function.getParameters().get(0);
				return Arrays.asList("CASE WHEN ", stringValue, " THEN 'true' WHEN not(", stringValue, ") THEN 'false' END"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			}
		});
	    //number -> boolean
	    convertModifier.addTypeConversion(new FunctionModifier() {
		    @Override
		    public List<?> translate(Function function) {
			    Expression param = function.getParameters().get(0);
			    if (Number.class.isAssignableFrom(param.getType())) {
				    return Arrays.asList(param, "::int::bool"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			    }
				return null;
		    }
	    }, FunctionModifier.BOOLEAN);
    	registerFunctionModifier(SourceSystemFunctions.CONVERT, convertModifier);

    	registerFunctionModifier("LEAD", new LeadLagModifier());
    	registerFunctionModifier("LAG", new LeadLagModifier());

    	DVCaller.call(85, this);
    }

    @Override
	public void initCapabilities(Connection connection)
			throws TranslatorException {
		super.initCapabilities(connection);
		if (this.postGisVersion.compareTo(Version.DEFAULT_VERSION) != 0) {
			return;
		}
		Statement s = null;
		ResultSet rs = null;
		try {
			s = connection.createStatement();
    	    rs = s.executeQuery("SELECT 1 FROM pg_catalog.pg_proc where lower(proname) = 'postgis_full_version'"); //$NON-NLS-1$
	    	if (!rs.next()) {
	    	    //no such function breakout
	    	    return;
	    	}
	    	rs.close();
    	    rs = s.executeQuery("SELECT PostGIS_Full_Version()"); //$NON-NLS-1$
			rs.next();
			String versionInfo = rs.getString(1);
			if (versionInfo != null) {
				if (versionInfo.contains("PROJ=")) { //$NON-NLS-1$
					projSupported = true;
				}
				int index = versionInfo.indexOf("POSTGIS=");
				if (index > -1) {
					String version = versionInfo.substring(index+9, versionInfo.indexOf('"', index+9));
					this.setPostGisVersion(version);
				}
			}
		} catch (SQLException e) {
			LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Could not determine PostGIS version"); //$NON-NLS-1$
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {

			}
			try {
				if (s != null) {
					s.close();
				}
			} catch (SQLException e) {

			}
		}
	}

    @Override
    public String translateLiteralBoolean(Boolean booleanValue) {
        if(booleanValue.booleanValue()) {
            return "TRUE"; //$NON-NLS-1$
        }
        return "FALSE"; //$NON-NLS-1$
    }

    @Override
    public String translateLiteralDate(Date dateValue) {
        return "DATE '" + formatDateValue(dateValue) + "'"; //$NON-NLS-1$//$NON-NLS-2$
    }

    @Override
    public String translateLiteralTime(Time timeValue) {
        return "TIME '" + formatDateValue(timeValue) + "'"; //$NON-NLS-1$//$NON-NLS-2$
    }

    @Override
    public String translateLiteralTimestamp(Timestamp timestampValue) {
        return "TIMESTAMP '" + formatDateValue(timestampValue) + "'"; //$NON-NLS-1$//$NON-NLS-2$
    }

    @Override
    public int getTimestampNanoPrecision() {
    	return 6;
    }

	@Override
    public List<?> translateLimit(Limit limit, ExecutionContext context) {
    	if (limit.getRowOffset() > 0) {
    		return Arrays.asList("LIMIT ", limit.getRowLimit(), " OFFSET ", limit.getRowOffset()); //$NON-NLS-1$ //$NON-NLS-2$
    	}
        return null;
    }

    /**
     * Postgres doesn't provide min/max(boolean), so this conversion writes a min(BooleanValue) as
     * bool_and(BooleanValue)
     * @see org.teiid.language.visitor.LanguageObjectVisitor#visit(org.teiid.language.AggregateFunction)
     * @since 4.3
     */
    @Override
    public List<?> translate(LanguageObject obj, ExecutionContext context, boolean isUpdate) {
    	if (obj instanceof AggregateFunction) {
    		AggregateFunction agg = (AggregateFunction)obj;
    		if (agg.getParameters().size() == 1 && TypeFacility.RUNTIME_TYPES.BOOLEAN.equals(agg.getParameters().get(0).getType())) {
            	if (agg.getName().equalsIgnoreCase(NonReserved.MIN)) {
            		agg.setName("bool_and"); //$NON-NLS-1$
            	} else if (agg.getName().equalsIgnoreCase(NonReserved.MAX)) {
            		agg.setName("bool_or"); //$NON-NLS-1$
            	}
            }
    	} else if (obj instanceof Like) {
    		Like like = (Like)obj;
    		if (like.getMode() == MatchMode.REGEX) {
    			return Arrays.asList(like.getLeftExpression(), like.isNegated()?" !~ ":" ~ ", like.getRightExpression()); //$NON-NLS-1$ //$NON-NLS-2$
    		}
    	} else if (obj instanceof ColumnReference) {
    		ColumnReference ref = (ColumnReference) obj;
    		Column mobj = ref.getMetadataObject();
			if (mobj != null && !isUpdate && mobj.getProperty(UUID, false) != null)
				return Arrays.asList(ref.getTable().getCorrelationName(), ".",ref.getName(), "::varchar");
			if (mobj != null && !isUpdate && (mobj.getProperty(JSON, false) != null || mobj.getProperty(JSONB, false) != null))
				return Arrays.asList(ref.toString(), "::text");
    	}
    	return super.translate(obj, context, isUpdate);
    }

    @Override
    public NullOrder getDefaultNullOrder() {
    	return NullOrder.HIGH;
    }

    @Override
    public boolean supportsOrderByNullOrdering() {
    	return getVersion().compareTo(EIGHT_4) >= 0;
    }

    @Override
    public List<String> getSupportedFunctions() {
        List<String> supportedFunctions = new ArrayList<String>();
        supportedFunctions.addAll(super.getSupportedFunctions());

        supportedFunctions.add("ABS"); //$NON-NLS-1$
        supportedFunctions.add("ACOS"); //$NON-NLS-1$
        supportedFunctions.add("ASIN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN2"); //$NON-NLS-1$
        supportedFunctions.add("BITAND"); //$NON-NLS-1$
        supportedFunctions.add("BITNOT"); //$NON-NLS-1$
        supportedFunctions.add("BITOR"); //$NON-NLS-1$
        supportedFunctions.add("BITXOR"); //$NON-NLS-1$
        supportedFunctions.add("CEILING"); //$NON-NLS-1$
        supportedFunctions.add("COS"); //$NON-NLS-1$
        supportedFunctions.add("COT"); //$NON-NLS-1$
        supportedFunctions.add("DEGREES"); //$NON-NLS-1$
        supportedFunctions.add("EXP"); //$NON-NLS-1$
        supportedFunctions.add("FLOOR"); //$NON-NLS-1$
        // These should not be pushed down since the grammar for string conversion is different
//        supportedFunctions.add("FORMATBIGDECIMAL"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATBIGINTEGER"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATDOUBLE"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATFLOAT"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATINTEGER"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATLONG"); //$NON-NLS-1$
        supportedFunctions.add("LOG"); //$NON-NLS-1$
        supportedFunctions.add("LOG10"); //$NON-NLS-1$
        supportedFunctions.add("MOD"); //$NON-NLS-1$
        supportedFunctions.add("PI"); //$NON-NLS-1$
        supportedFunctions.add("POWER"); //$NON-NLS-1$
        supportedFunctions.add("RADIANS"); //$NON-NLS-1$
        supportedFunctions.add("ROUND"); //$NON-NLS-1$
        supportedFunctions.add("SIGN"); //$NON-NLS-1$
        supportedFunctions.add("SIN"); //$NON-NLS-1$
        supportedFunctions.add("SQRT"); //$NON-NLS-1$
        supportedFunctions.add("TAN"); //$NON-NLS-1$

	    supportedFunctions.add(SourceSystemFunctions.ASCII);
        supportedFunctions.add("CHR"); //$NON-NLS-1$
        supportedFunctions.add("CHAR"); //$NON-NLS-1$
        supportedFunctions.add("||"); //$NON-NLS-1$
        supportedFunctions.add("CONCAT"); //$NON-NLS-1$
        supportedFunctions.add("INITCAP"); //$NON-NLS-1$
        supportedFunctions.add("LCASE"); //$NON-NLS-1$
        supportedFunctions.add("LEFT"); //$NON-NLS-1$
        supportedFunctions.add("LENGTH"); //$NON-NLS-1$
        supportedFunctions.add("LOCATE"); //$NON-NLS-1$
        supportedFunctions.add("LOWER"); //$NON-NLS-1$
        supportedFunctions.add("LPAD"); //$NON-NLS-1$
        supportedFunctions.add("LTRIM"); //$NON-NLS-1$
	    supportedFunctions.add(SourceSystemFunctions.RAND);
        supportedFunctions.add("REPEAT"); //$NON-NLS-1$
        supportedFunctions.add("REPLACE"); //$NON-NLS-1$
        supportedFunctions.add("RIGHT"); //$NON-NLS-1$
        supportedFunctions.add("RPAD"); //$NON-NLS-1$
        supportedFunctions.add("RTRIM"); //$NON-NLS-1$
        supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.TRIM);
        supportedFunctions.add("UCASE"); //$NON-NLS-1$
        supportedFunctions.add("UPPER"); //$NON-NLS-1$

        // These are executed within the server and never pushed down
//        supportedFunctions.add("CURDATE"); //$NON-NLS-1$
//        supportedFunctions.add("CURTIME"); //$NON-NLS-1$
//        supportedFunctions.add("NOW"); //$NON-NLS-1$
        supportedFunctions.add("DAYNAME"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFMONTH"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFWEEK"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFYEAR"); //$NON-NLS-1$
        // These should not be pushed down since the grammar for string conversion is different
//        supportedFunctions.add("FORMATDATE"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATTIME"); //$NON-NLS-1$
//        supportedFunctions.add("FORMATTIMESTAMP"); //$NON-NLS-1$
        supportedFunctions.add("HOUR"); //$NON-NLS-1$
        supportedFunctions.add("MINUTE"); //$NON-NLS-1$
        supportedFunctions.add("MONTH"); //$NON-NLS-1$
        supportedFunctions.add("MONTHNAME"); //$NON-NLS-1$
        // These should not be pushed down since the grammar for string conversion is different
//        supportedFunctions.add("PARSEDATE"); //$NON-NLS-1$
//        supportedFunctions.add("PARSETIME"); //$NON-NLS-1$
//        supportedFunctions.add("PARSETIMESTAMP"); //$NON-NLS-1$
        supportedFunctions.add("QUARTER"); //$NON-NLS-1$
        supportedFunctions.add("SECOND"); //$NON-NLS-1$
        if (this.getVersion().compareTo(EIGHT_2) >= 0) {
        	supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$
			supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$
        }
        if (this.getClass().getSimpleName().equals("PostgreSQLExecutionFactory") && this.getVersion().compareTo(SEVEN_4) >= 0) {
            supportedFunctions.add("TIMESTAMPCREATE"); //$NON-NLS-1$
        }
        supportedFunctions.add("WEEK"); //$NON-NLS-1$
        supportedFunctions.add("YEAR"); //$NON-NLS-1$

        supportedFunctions.add("CAST"); //$NON-NLS-1$
        supportedFunctions.add("CONVERT"); //$NON-NLS-1$
        supportedFunctions.add("IFNULL"); //$NON-NLS-1$
        supportedFunctions.add("NVL"); //$NON-NLS-1$

        // Additional functions
//        // Math
//        supportedFunctions.add("%"); //$NON-NLS-1$
//        supportedFunctions.add("^"); //$NON-NLS-1$
//        supportedFunctions.add("|/"); //$NON-NLS-1$
//        supportedFunctions.add("||/"); //$NON-NLS-1$
//        supportedFunctions.add("!"); //$NON-NLS-1$
//        supportedFunctions.add("!!"); //$NON-NLS-1$
//        supportedFunctions.add("@"); //$NON-NLS-1$
//          // Bit manipulation
//        supportedFunctions.add("&"); //$NON-NLS-1$
//        supportedFunctions.add("|"); //$NON-NLS-1$
//        supportedFunctions.add("#"); //$NON-NLS-1$
//        supportedFunctions.add("~"); //$NON-NLS-1$
//        supportedFunctions.add("<<"); //$NON-NLS-1$
//        supportedFunctions.add(">>"); //$NON-NLS-1$
//
//        supportedFunctions.add("CBRT"); //$NON-NLS-1$
//        supportedFunctions.add("CEIL"); //$NON-NLS-1$
//        supportedFunctions.add("LN"); //$NON-NLS-1$
//        supportedFunctions.add("MOD"); //$NON-NLS-1$
//        supportedFunctions.add("RANDOM"); //$NON-NLS-1$
//        supportedFunctions.add("SETSEED"); //$NON-NLS-1$
//        supportedFunctions.add("TRUNC"); //$NON-NLS-1$
//        supportedFunctions.add("WIDTH_BUCKET"); //$NON-NLS-1$
//
//        // String
//        supportedFunctions.add("BIT_LENGTH"); //$NON-NLS-1$
//        supportedFunctions.add("BTRIM"); //$NON-NLS-1$
//        supportedFunctions.add("CHAR_LENGTH"); //$NON-NLS-1$
//        supportedFunctions.add("CHARACTER_LENGTH"); //$NON-NLS-1$
//        supportedFunctions.add("DECODE"); //$NON-NLS-1$
//        supportedFunctions.add("ENCODE"); //$NON-NLS-1$
//        supportedFunctions.add("MD5"); //$NON-NLS-1$
//        supportedFunctions.add("OCTET_LENGTH"); //$NON-NLS-1$
//        supportedFunctions.add("PG_CLIENT_ENCODING"); //$NON-NLS-1$
//        supportedFunctions.add("QUOTE_IDENT"); //$NON-NLS-1$
//        supportedFunctions.add("QUOTE_LITERAL"); //$NON-NLS-1$
//        supportedFunctions.add("SPLIT_PART"); //$NON-NLS-1$
//        supportedFunctions.add("STRPOS"); //$NON-NLS-1$
//        supportedFunctions.add("SUBSTR"); //$NON-NLS-1$
//        supportedFunctions.add("TO_ASCII"); //$NON-NLS-1$
//        supportedFunctions.add("TO_HEX"); //$NON-NLS-1$
//        supportedFunctions.add("TRANSLATE"); //$NON-NLS-1$
//
//        // Bit operations
//        supportedFunctions.add("GET_BIT"); //$NON-NLS-1$
//        supportedFunctions.add("GET_BYTE"); //$NON-NLS-1$
//        supportedFunctions.add("SET_BIT"); //$NON-NLS-1$
//        supportedFunctions.add("SET_BYTE"); //$NON-NLS-1$
//
//        // Formatting
//        supportedFunctions.add("TO_CHAR"); //$NON-NLS-1$
//        supportedFunctions.add("TO_DATE"); //$NON-NLS-1$
//        supportedFunctions.add("TO_TIMESTAMP"); //$NON-NLS-1$
//        supportedFunctions.add("TO_NUMBER"); //$NON-NLS-1$
//
//        // Date / Time
//        supportedFunctions.add("AGE"); //$NON-NLS-1$
//        supportedFunctions.add("CURRENT_DATE"); //$NON-NLS-1$            // no ()
//        supportedFunctions.add("CURRENT_TIME"); //$NON-NLS-1$            // no ()
//        supportedFunctions.add("CURRENT_TIMESTAMP"); //$NON-NLS-1$       // no ()
//        supportedFunctions.add("DATE_PART"); //$NON-NLS-1$
//        supportedFunctions.add("DATE_TRUNC"); //$NON-NLS-1$
//        supportedFunctions.add("ISFINITE"); //$NON-NLS-1$
//        supportedFunctions.add("JUSTIFY_HOURS"); //$NON-NLS-1$
//        supportedFunctions.add("JUSTIFY_DAYS"); //$NON-NLS-1$
//        supportedFunctions.add("LOCALTIME"); //$NON-NLS-1$               // no ()
//        supportedFunctions.add("LOCALTIMESTAMP"); //$NON-NLS-1$          // no ()
//        supportedFunctions.add("TIMEOFDAY"); //$NON-NLS-1$
//
//        // Conditional
          supportedFunctions.add("COALESCE"); //$NON-NLS-1$
//        supportedFunctions.add("NULLIF"); //$NON-NLS-1$
//        supportedFunctions.add("GREATEST"); //$NON-NLS-1$
//        supportedFunctions.add("LEAST"); //$NON-NLS-1$
//
//        // Network Addresses
////        supportedFunctions.add("BROADCAST"); //$NON-NLS-1$
////        supportedFunctions.add("HOST"); //$NON-NLS-1$
////        supportedFunctions.add("MASKLEN"); //$NON-NLS-1$
////        supportedFunctions.add("SET_MASKLEN"); //$NON-NLS-1$
////        supportedFunctions.add("NETMASK"); //$NON-NLS-1$
////        supportedFunctions.add("HOSTMASK"); //$NON-NLS-1$
////        supportedFunctions.add("NETWORK"); //$NON-NLS-1$
////        supportedFunctions.add("TEXT"); //$NON-NLS-1$
////        supportedFunctions.add("ABBREV"); //$NON-NLS-1$
////        supportedFunctions.add("FAMILY"); //$NON-NLS-1$
////        supportedFunctions.add("TRUNC"); //$NON-NLS-1$
//
//        // Set generator
//        supportedFunctions.add("GENERATE_SERIES"); //$NON-NLS-1$
//
//        // Information
//        supportedFunctions.add("CURRENT_DATABASE"); //$NON-NLS-1$
//        supportedFunctions.add("CURRENT_SCHEMA"); //$NON-NLS-1$
//        supportedFunctions.add("CURRENT_SCHEMAS"); //$NON-NLS-1$
//        supportedFunctions.add("CURRENT_USER"); //$NON-NLS-1$           // no ()
//        supportedFunctions.add("INET_CLIENT_ADDR"); //$NON-NLS-1$
//        supportedFunctions.add("INET_CLIENT_PORT"); //$NON-NLS-1$
//        supportedFunctions.add("INET_SERVER_ADDR"); //$NON-NLS-1$
//        supportedFunctions.add("INET_SERVER_PORT"); //$NON-NLS-1$
//        supportedFunctions.add("SESSION_USER"); //$NON-NLS-1$           // no ()
//        supportedFunctions.add("USER"); //$NON-NLS-1$                   // no ()
//        supportedFunctions.add("VERSION"); //$NON-NLS-1$
//
        supportedFunctions.add(SourceSystemFunctions.ARRAY_GET);
        supportedFunctions.add(SourceSystemFunctions.ARRAY_LENGTH);
        supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP);
        supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP);
        supportedFunctions.add(SourceSystemFunctions.SPLIT_PART);

	    if (this.postGisVersion.compareTo(ONE_3) >= 0) {
		    supportedFunctions.add(SourceSystemFunctions.ST_ASBINARY);
		    supportedFunctions.add(SourceSystemFunctions.ST_ASTEXT);
		    supportedFunctions.add(SourceSystemFunctions.ST_CONTAINS);
		    supportedFunctions.add(SourceSystemFunctions.ST_CROSSES);
		    supportedFunctions.add(SourceSystemFunctions.ST_DISJOINT);
		    supportedFunctions.add(SourceSystemFunctions.ST_DISTANCE);
		    supportedFunctions.add(SourceSystemFunctions.ST_EQUALS);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMTEXT);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMWKB);
		    supportedFunctions.add(SourceSystemFunctions.ST_INTERSECTS);
		    supportedFunctions.add(SourceSystemFunctions.ST_OVERLAPS);
		    supportedFunctions.add(SourceSystemFunctions.ST_SETSRID);
		    supportedFunctions.add(SourceSystemFunctions.ST_SRID);
		    supportedFunctions.add(SourceSystemFunctions.ST_TOUCHES);
		    supportedFunctions.add(SourceSystemFunctions.ST_HASARC);
		    supportedFunctions.add(SourceSystemFunctions.ST_SIMPLIFY);
		    supportedFunctions.add(SourceSystemFunctions.ST_FORCE_2D);
		    supportedFunctions.add(SourceSystemFunctions.ST_ENVELOPE);
		    supportedFunctions.add(SourceSystemFunctions.ST_WITHIN);
		    supportedFunctions.add(SourceSystemFunctions.ST_DWITHIN);
		    supportedFunctions.add(SourceSystemFunctions.ST_EXTENT);
		    supportedFunctions.add(SourceSystemFunctions.DOUBLE_AMP_OP);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMEWKT);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMEWKB);
		    supportedFunctions.add(SourceSystemFunctions.ST_ASEWKB);
		    supportedFunctions.add(SourceSystemFunctions.ST_ASEWKT);
		    supportedFunctions.add(SourceSystemFunctions.ST_AREA);
		    supportedFunctions.add(SourceSystemFunctions.ST_BOUNDARY);
		    supportedFunctions.add(SourceSystemFunctions.ST_BUFFER);
		    supportedFunctions.add(SourceSystemFunctions.ST_CENTROID);
		    supportedFunctions.add(SourceSystemFunctions.ST_COORDDIM);
		    supportedFunctions.add(SourceSystemFunctions.ST_CONVEXHULL);
		    supportedFunctions.add(SourceSystemFunctions.ST_DIFFERENCE);
		    supportedFunctions.add(SourceSystemFunctions.ST_DIMENSION);
		    supportedFunctions.add(SourceSystemFunctions.ST_ENDPOINT);
		    supportedFunctions.add(SourceSystemFunctions.ST_EXTERIORRING);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMETRYN);
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMETRYTYPE);
		    supportedFunctions.add(SourceSystemFunctions.ST_INTERIORRINGN);
		    supportedFunctions.add(SourceSystemFunctions.ST_ISCLOSED);
		    supportedFunctions.add(SourceSystemFunctions.ST_ISEMPTY);
		    supportedFunctions.add(SourceSystemFunctions.ST_ISRING);
		    supportedFunctions.add(SourceSystemFunctions.ST_ISSIMPLE);
		    supportedFunctions.add(SourceSystemFunctions.ST_ISVALID);
		    supportedFunctions.add(SourceSystemFunctions.ST_LENGTH);
		    supportedFunctions.add(SourceSystemFunctions.ST_NUMGEOMETRIES);
		    supportedFunctions.add(SourceSystemFunctions.ST_NUMINTERIORRINGS);
		    supportedFunctions.add(SourceSystemFunctions.ST_NUMPOINTS);
		    supportedFunctions.add(SourceSystemFunctions.ST_ORDERINGEQUALS);
		    supportedFunctions.add(SourceSystemFunctions.ST_PERIMETER);
		    supportedFunctions.add(SourceSystemFunctions.ST_POINT);
		    supportedFunctions.add(SourceSystemFunctions.ST_POINTN);
		    supportedFunctions.add(SourceSystemFunctions.ST_POINTONSURFACE);
		    supportedFunctions.add(SourceSystemFunctions.ST_POLYGON);
		    supportedFunctions.add(SourceSystemFunctions.ST_RELATE);
		    supportedFunctions.add(SourceSystemFunctions.ST_STARTPOINT);
		    supportedFunctions.add(SourceSystemFunctions.ST_SYMDIFFERENCE);
		    supportedFunctions.add(SourceSystemFunctions.ST_UNION);
		    supportedFunctions.add(SourceSystemFunctions.ST_X);
		    supportedFunctions.add(SourceSystemFunctions.ST_Y);
	    }
	    if (this.getVersion().compareTo(EIGHT_0) >= 0) {
		}
	    if (this.postGisVersion.compareTo(ONE_4) >= 0) {
		    // this will be introduced in the scope of https://github.com/teiid/teiid/commit/1f45f4f25cd427ae2624cac2226589383e4996e6 commit
		    //supportedFunctions.add(SourceSystemFunctions.ST_ASGEOJSON);
		    supportedFunctions.add(SourceSystemFunctions.ST_ASGML);
		    supportedFunctions.add(SourceSystemFunctions.ST_CURVETOLINE);
        	supportedFunctions.add(SourceSystemFunctions.ST_Z);
	    }
	    if (this.postGisVersion.compareTo(ONE_5) >= 0) {
		    supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMGML);
	    }
	    if (this.postGisVersion.compareTo(TWO_0) >= 0) {
		    // this will be introduced in the scope of https://github.com/teiid/teiid/commit/1f45f4f25cd427ae2624cac2226589383e4996e6 commit
		    //supportedFunctions.add(SourceSystemFunctions.ST_GEOMFROMGEOJSON);
	    }
	    if (this.projSupported) {
		    supportedFunctions.add(SourceSystemFunctions.ST_TRANSFORM);
		    supportedFunctions.add(SourceSystemFunctions.ST_ASKML);
	    }
	    supportedFunctions.add("pg_catalog.encode"); //$NON-NLS-1$
        return supportedFunctions;
    }

    /**
     * This is true only after Postgre version 7.1
     * However, since version 7 was released in 2000 we'll assume a post 7 instance.
     */
    public boolean supportsInlineViews() {
		return true;
	}

	@Override
	public Boolean supportsStringAgg() {
		if (supportsStringAgg == null || supportsStringAgg == true)
			return getVersion().compareTo(NINE_0) >= 0;
		return false;
	}

    @Override
    public boolean supportsRowLimit() {
        return true;
    }
    @Override
    public boolean supportsRowOffset() {
        return true;
    }

    @Override
    public boolean supportsExcept() {
        return true;
    }

    @Override
    public boolean supportsIntersect() {
        return true;
    }

    @Override
    public boolean supportsAggregatesEnhancedNumeric() {
    	return getVersion().compareTo(EIGHT_2) >= 0;
    }

	@Override
	public boolean supportsCommonTableExpressions() {
		return (super.supportsCommonTableExpressions() && (getVersion().compareTo(EIGHT_4) >= 0));
	}

	@Override
	public boolean supportsRecursiveCommonTableExpressions() {
		return (super.supportsRecursiveCommonTableExpressions() && supportsCommonTableExpressions());
	}

	@Override
    public boolean supportsArrayAgg() {
	    return false;
    	//return getVersion().compareTo(EIGHT_4) >= 0;
    }

    @Override
    public boolean supportsElementaryOlapOperations() {
    	return getVersion().compareTo(EIGHT_4) >= 0;
    }

    @Override
    public boolean supportsWindowDistinctAggregates() {
    	return false;
    }

    @Override
    public boolean supportsSimilarTo() {
    	return true;
    }

    @Override
    public boolean supportsLikeRegex() {
    	return true;
    }

    @Override
    public boolean supportsOnlyFormatLiterals() {
    	return true;
    }

    @Override
    public boolean supportsFormatLiteral(String literal,
    		org.teiid.translator.ExecutionFactory.Format format) {
    	if (format == Format.NUMBER) {
    		return false;
    	}
    	return OracleFormatFunctionModifier.supportsLiteral(literal);
    }

    @Override
    public boolean supportsArrayType() {
    	return true;
    }

	@Override
	public boolean supportsSelectExpressionArrayType() {
		return false;
	}

	@Override
	protected boolean usesDatabaseVersion() {
		return true;
	}

    // BUG: Pushdown Group by <scalar function> construct for the Rest of JDBC Datasources
    // ISSUE: DV-48
    // SOLUTION: indicated that the underlying DB supports functions in group by clause
    // FIXED BY: Roman Pedchenko
    // DATE: 2013.01.09
	@Override
	public boolean supportsFunctionsInGroupBy() {
		return true;
	}
	// BUG END

    @Override
    public boolean useStreamsForLobs() {
        return true;
    }

    // EXTENSION
	// ISSUE: DVCORE-235,DVCORE-320,DVCORE-787,DVCORE-1094,DVCORE-1172,DVCORE-2118,DVCORE-2136,DVCORE-2162,DVCORE-2279
	// FIXED BY: Roman Pedchenko, Salvatore Raunich (DVCORE-2118, DVCORE-2162, DVCORE-2279)
	// DATE: 2013.02.01,2013.03.01,2013.11.12,2014.04.10,2014.04.30,2014.06.20,2014.06.27,2014.07.31,2014.09.01
	private boolean useStreamingResults = true;

	@TranslatorProperty(description="Controls using streaming mode for PostgreSQL result sets.", display="Use Streaming Results")
	public boolean isUseStreamingResults() {
		return useStreamingResults;
	}

	public void setUseStreamingResults(boolean useStreamingResults) {
		this.useStreamingResults = useStreamingResults;
	}

	@Override
	public ResultSetExecution createResultSetExecution(QueryExpression command, ExecutionContext executionContext, RuntimeMetadata metadata, Connection conn) throws TranslatorException {
		//TODO: This is not correct; this should be only called once for connection creation
		obtainedConnection(conn);
		return new PostgresqlJDBCQueryExecution(command, conn, executionContext, this, isUseStreamingResults());
	}

	@Override
    public ProcedureExecution createDirectExecution(List<Argument> arguments, Command command, ExecutionContext executionContext, RuntimeMetadata metadata, Connection conn)
    		throws TranslatorException {
    	obtainedConnection(conn);
    	return new PostgreSQLJDBCDirectQueryExecution(arguments, command, conn, executionContext, this, isUseStreamingResults());
    }

	private final class LeadLagModifier extends FunctionModifier {
		@Override
		public List<?> translate(Function function) {
			if (function.getParameters().size() > 2) {
				Expression ex = function.getParameters().get(0);
				Expression ex2 = function.getParameters().get(2);
				if (!ex.getType().equals(ex2.getType()) || ex2 instanceof Literal) {
					function.getParameters().set(2, new Function(SourceSystemFunctions.CONVERT,
							Arrays.asList(ex2, new Literal(DataTypeManager.getDataTypeName(ex.getType()), TypeFacility.RUNTIME_TYPES.STRING)),
							ex.getType())
					);
				}
			}
			return null;
		}
	}

	static class PostgresqlJDBCQueryExecution extends JDBCQueryExecution {
		private final boolean useStreamingResults;
		private boolean commitType = false;

		public PostgresqlJDBCQueryExecution(Command command, Connection connection, ExecutionContext context, JDBCExecutionFactory env, boolean useStreamingResults) {
			super(command, connection, context, env);
			this.useStreamingResults = useStreamingResults;
		}

		@Override
		public void addStatementWarnings() throws SQLException {
			// intentionally left blank
		}

		@Override
		protected synchronized Statement getStatement() throws SQLException {
			if (statement != null) {
				statement.close();
				statement = null;
			}
			if (useStreamingResults)
				statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			else
				statement = connection.createStatement();
			setSizeContraints(statement);
			return statement;
		}

		@Override
		protected synchronized CallableStatement getCallableStatement(String sql) throws SQLException {
			if (statement != null) {
				statement.close();
				statement = null;
			}
			if (useStreamingResults)
				statement = connection.prepareCall(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			else
				statement = connection.prepareCall(sql);
			setSizeContraints(statement);
			return (CallableStatement)statement;
		}

		@Override
		protected synchronized PreparedStatement getPreparedStatement(String sql) throws SQLException {
			if (statement != null) {
				statement.close();
				statement = null;
			}
			if (useStreamingResults)
				statement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			else
				statement = connection.prepareStatement(sql);
			setSizeContraints(statement);
			return (PreparedStatement)statement;
		}

		@Override
		public void execute() throws TranslatorException {
			if (!useStreamingResults) {
				super.execute();
				return;
			}
//			boolean commitType = false;
			try {
				commitType = connection.getAutoCommit();
			} catch (SQLException e) {
				throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94092));
			}
			if (commitType) {
				try {
					connection.setAutoCommit(false);
				} catch (SQLException e) {
					throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94093));
				}
			}
			try {
				super.execute();
			} catch (TranslatorException e) {
				if (commitType)
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				throw e;
			} finally {
				if (commitType) {
					try {
//						connection.commit();
						connection.setAutoCommit(true);
					} catch (SQLException e) {
						LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception closing"); //$NON-NLS-1$
					}
				}
			}
		}

		@Override
		public synchronized void close() {
			// first we would need to close the result set here then we can close
			// the statement, using the base class.
			try {
				if (results != null) {
					try {
						results.close();
						results = null;
					} catch (SQLException e) {
						LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception closing"); //$NON-NLS-1$
					}
				}
				try {
					if (useStreamingResults && commitType) {
						connection.commit();
					}
				} catch (SQLException e) {
					LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception committing"); //$NON-NLS-1$
				}
			} finally {
				super.close();
			}
		}
	}
	// END EXTENSION

	static class PostgreSQLJDBCDirectQueryExecution extends JDBCDirectQueryExecution {
		private final boolean useStreamingResults;
		private boolean commitType = false;

		public PostgreSQLJDBCDirectQueryExecution(List<Argument> arguments, Command command, Connection connection, ExecutionContext context, JDBCExecutionFactory env, boolean useStreamingResults) {
			super(arguments, command, connection, context, env);
			this.useStreamingResults = useStreamingResults;
		}

		@Override
		protected synchronized Statement getStatement() throws SQLException {
			if (statement != null) {
				statement.close();
				statement = null;
			}
			if (useStreamingResults)
				statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			else
				statement = connection.createStatement();
			setSizeContraints(statement);
			return statement;
		}

		@Override
		public void execute() throws TranslatorException {
			if (!useStreamingResults) {
				super.execute();
				return;
			}
//			boolean commitType = false;
			try {
				commitType = connection.getAutoCommit();
			} catch (SQLException e) {
				throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94092));
			}
			if (commitType) {
				try {
					connection.setAutoCommit(false);
				} catch (SQLException e) {
					throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94093));
				}
			}
			try {
				super.execute();
			} catch (TranslatorException e) {
				if (commitType)
					try {
						connection.rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				throw e;
			} finally {
				if (commitType) {
					try {
//						connection.commit();
						connection.setAutoCommit(true);
					} catch (SQLException e) {
						LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception closing"); //$NON-NLS-1$
					}
				}
			}
		}

		@Override
		public synchronized void close() {
			// first we would need to close the result set here then we can close
			// the statement, using the base class.
			try {
				if (results != null) {
					try {
						results.close();
						results = null;
					} catch (SQLException e) {
						LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception closing"); //$NON-NLS-1$
					}
				}
				try {
					if (useStreamingResults && commitType) {
						connection.commit();
					}
				} catch (SQLException e) {
					LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception committing"); //$NON-NLS-1$
				}
			} finally {
				super.close();
			}
		}
	}

	// EXTENSION
	// ISSUE: DV-79,DVCORE-1172,DVCORE-2162,DVCORE-2279
	// FIXED BY: Roman Pedchenko, Salvatore Raunich
	// DATE: 2013.05.07,2014.04.30,2014.07.31,2014.09.01
	@Override
	public UpdateExecution createUpdateExecution(Command command, ExecutionContext executionContext, RuntimeMetadata metadata, Connection conn)
			throws TranslatorException {
		//TODO: This is not correct; this should be only called once for connection creation
		obtainedConnection(conn);
		return new PostgresqlJDBCUpdateExecution(command, conn, executionContext, this);
	}

	static class PostgresqlJDBCUpdateExecution extends JDBCUpdateExecution {

		public PostgresqlJDBCUpdateExecution(Command command, Connection connection, ExecutionContext context, JDBCExecutionFactory env) {
			super(command, connection, context, env);
		}

		@Override
		public void execute() throws TranslatorException {
			boolean commitType = false;
			try {
				commitType = connection.getAutoCommit();
			} catch (SQLException e) {
				throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94092));
			}
			if (commitType) {
				try {
					connection.setAutoCommit(false);
				} catch (SQLException e) {
					throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94093));
				}
			}
			try {
				super.execute();
			} finally {
				if (commitType) {
					try {
						connection.commit();
						connection.setAutoCommit(true);
					} catch (SQLException e) {
						throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94094));
				}
				}
			}
		}
	}
	// END EXTENSION

	// EXTENSION
	// ISSUE: DVCORE-1208
	// FIXED BY: Salvatore Raunich
	// DATE: 2014.07.02
	public ITypeConverter getTypeConverter() {
		return new ITypeConverter() {
			@Override
			public String getRuntimeType(int type, String typeName, int precision, int scale) {
                if ("TEXT".equalsIgnoreCase(typeName)) {
                    return DataTypeManager.DefaultDataTypes.CLOB;
                }
                if ("BYTEA".equalsIgnoreCase(typeName)) {
                    return DataTypeManager.DefaultDataTypes.BLOB;
                }
				if (UUID_TYPE.equalsIgnoreCase(typeName)) {
					return TypeFacility.RUNTIME_NAMES.STRING;
				}
				if (JSON.equalsIgnoreCase(typeName)) {
					return DataTypeManager.DefaultDataTypes.CLOB;
				}
				if (JSONB.equalsIgnoreCase(typeName)) {
					return DataTypeManager.DefaultDataTypes.CLOB;
				}
				if (CITEXT_TYPE.equalsIgnoreCase(typeName)) {
				    return DataTypeManager.DefaultDataTypes.CLOB;
                }                if (type == Types.CHAR) {
                    if (precision == 1)
                        return DataTypeManager.DefaultDataTypes.CHAR;
                    return DataTypeManager.DefaultDataTypes.STRING;
                }
                if (TSVECTOR.equalsIgnoreCase(typeName)) {
                    return DataTypeManager.DefaultDataTypes.CLOB;
                }
                if (TSQUERY.equalsIgnoreCase(typeName)) {
                    return DataTypeManager.DefaultDataTypes.CLOB;
                }
                if (GEOMETRY.equalsIgnoreCase(typeName)) {
                    return DataTypeManager.DefaultDataTypes.CLOB;
                }
				return null;
			}

            @Override
            protected String usualNumericCheck(String typeName, int precision, int scale) {
                if ("NUMERIC".equalsIgnoreCase(typeName) ||
                        "DECIMAL".equalsIgnoreCase(typeName) ||
                        "NUMBER".equalsIgnoreCase(typeName)) {
                    if (precision == 0 && scale == 0)
                        return DataTypeManager.DefaultDataTypes.BIG_DECIMAL;
                }
                return super.usualNumericCheck(typeName, precision, scale);
            }

			@Override
			public void tuneColumn(Column column, int type, String typeName, int precision, int scale) {
                if (type == Types.NUMERIC && precision == 0 && scale == 0) {
                        column.setPrecision(131072);
                        column.setScale(16383);
                }
				if (UUID.equalsIgnoreCase(typeName)) {
					column.setProperty(UUID, UUID);
					int columnSize = 36;
					column.setPrecision(columnSize);
					column.setLength(columnSize);
                    column.setCaseSensitive(false);
				}
				if (JSON.equalsIgnoreCase(typeName)) {
					column.setProperty(JSON, JSON);
				}
				if (JSONB.equalsIgnoreCase(typeName)) {
					column.setProperty(JSONB, JSONB);
				}
			}
		};
	}
	// END EXTENSION

    // EXTENSION
    // ISSUE: DVCORE-2441
    // FIXED BY: Roman Pedchenko
    // DATE: 2014.10.23
    // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
    @Override
    public boolean supportsSelectWithoutFrom() {
    	return true;
    }
    // END EXTENSION

	private String replaceNullCharsWith = " ";

    @Override
	@TranslatorProperty(description="Replace null chars with a specified string.", display="Replace null chars with a specified string")
	public String isReplaceNullCharsWith() {
		return replaceNullCharsWith;
	}

	public void setReplaceNullCharsWith(String replaceNullCharsWith) {
		this.replaceNullCharsWith = replaceNullCharsWith;
	}

    // EXTENSION
    // ISSUE: DVCORE-2049
    // FIXED BY: Dmitry Pogorelov
    // DATE: 2014.12.09
    @Override
    public Object retrieveValue(ResultSet results, int columnIndex, Class<?> expectedType) throws SQLException {
	    if (expectedType == TypeFacility.RUNTIME_TYPES.JSON) {
		    return new JsonType(new ClobImpl(results.getString(columnIndex)));
	    }
        if (expectedType == TypeFacility.RUNTIME_TYPES.CLOB) {
            return results.getString(columnIndex);
        }
		if (Object.class == expectedType) {
			Object obj = results.getObject(columnIndex);
			if (obj != null) {
			    Object obj1 = convertSpecificObjects(obj);
			    if (obj1 != null)
			        return obj1;
			}
		}
        Object value = super.retrieveValue(results, columnIndex, expectedType);
		if (value instanceof Array) {
            value = convertArray(value);
        }
        return value;
    }

    private Object convertArray(Object value) throws SQLException {
        final Object[] array = (Object[]) ((Array) value).getArray();
        if (array != null)
            for (int i = 0; i < array.length; i++) {
                Object obj = array[i];
                if (obj != null) {
                    Object obj1 = convertSpecificObjects(obj);
                    if (obj1 != null)
                        array[i] = obj1;
                }
            }
        return new ArrayImpl(array);
    }

    private Object convertSpecificObjects(Object obj) throws SQLException {
        if (Timestamp.class.isAssignableFrom(obj.getClass()))
            return new Timestamp(((Timestamp) obj).getTime());
        // TODO here we may need to convert all PG* classes to strings or more specific objects
        String className = obj.getClass().getSimpleName();
        if ("PGInterval".equals(className))
            return obj.toString();
        else if ("PGobject".equals(className))
            return obj.toString();
        if (obj instanceof Array)
            return convertArray(obj);
        return null;
    }

    @Override
    public Object retrieveValue(CallableStatement results, int parameterIndex, Class<?> expectedType) throws SQLException {
	    if (expectedType == TypeFacility.RUNTIME_TYPES.JSON) {
		    return new JsonType(new ClobImpl(results.getString(parameterIndex)));
	    }
        if (expectedType == TypeFacility.RUNTIME_TYPES.CLOB) {
            return results.getString(parameterIndex);
        }
		if (Object.class == expectedType) {
			Object obj = results.getObject(parameterIndex);
            Object obj1 = convertSpecificObjects(obj);
            if (obj1 != null)
                return obj1;
		}
        Object value = super.retrieveValue(results, parameterIndex, expectedType);
        if (value instanceof Array) {
            value = convertArray(value);
        }
        return value;
    }

    @Override
    public void bindValue(PreparedStatement stmt, Object param, Class<?> paramType, int i) throws SQLException {
        if (paramType.equals(TypeFacility.RUNTIME_TYPES.CLOB)) {
            if (param instanceof Clob) {
                Clob clob = (Clob) param;
                String result = null;
                try {
                    result = ClobType.getString(clob);
                } catch (IOException e) {
                    throw new SQLException(e);
                }
                stmt.setString(i, replaceNullCharsIfNeeded(this, result));
            } else
		        stmt.setNull(i, Types.VARCHAR);
	        return;
        }
        if (param != null && (paramType.equals(TypeFacility.RUNTIME_TYPES.STRING) || paramType.equals(TypeFacility.RUNTIME_TYPES.CHAR))) {
            param = replaceNullCharsIfNeeded(this, param);
        }
	    if (param == null && (paramType == TypeFacility.RUNTIME_TYPES.BLOB
			    || paramType == TypeFacility.RUNTIME_TYPES.GEOMETRY
			    || paramType == TypeFacility.RUNTIME_TYPES.GEOGRAPHY)) {
            stmt.setNull(i, Types.VARBINARY);
            return;
        }
        super.bindValue(stmt, param, paramType, i);
    }
    // END EXTENSION

	@Override
	public void prepareBind(ColumnReference column, Literal obj, StringBuilder buffer) {
		Column mobj = column.getMetadataObject();
		if (mobj != null && mobj.getProperty(JSON, false) != null)
			buffer.append("::json");
        else if (mobj != null && mobj.getProperty(JSONB, false) != null)
            buffer.append("::jsonb");
        else if (this.getClass().equals(PostgreSQLExecutionFactory.class)) {
            // no successors, PG-only stuff
            if (mobj != null && TSVECTOR.equalsIgnoreCase(mobj.getNativeType()))
                buffer.append("::tsvector");
            else if (mobj != null && TSQUERY.equalsIgnoreCase(mobj.getNativeType()))
                buffer.append("::tsquery");
            else if (mobj != null && GEOMETRY.equalsIgnoreCase(mobj.getNativeType()))
                buffer.append("::geometry");
        }
	}

	@Override
    public SQLConversionVisitor getSQLConversionVisitor() {
        return new SQLConversionVisitor(this) {
	        @Override
	        protected void appendWithKeyword(With obj) {
		        super.appendWithKeyword(obj);
		        for (WithItem with : obj.getItems()) {
			        if (with.isRecusive()) {
				        buffer.append(SQLConstants.Tokens.SPACE);
				        buffer.append(SQLConstants.Reserved.RECURSIVE);
				        break;
				    }
			    }
		    }

            /**
             * Some literals in the select need a cast to prevent being seen as the unknown/string type
             */
            @Override
            public void visit(DerivedColumn obj) {
	            if (obj.getExpression() instanceof Literal) {
		            String castType = null;
		            if (obj.getExpression().getType() == TypeFacility.RUNTIME_TYPES.STRING) {
			            castType = "bpchar"; //$NON-NLS-1$
			        } else if (obj.getExpression().getType() == TypeFacility.RUNTIME_TYPES.VARBINARY
                            || obj.getExpression().getType() == TypeFacility.RUNTIME_TYPES.BLOB) {
			            castType = "bytea"; //$NON-NLS-1$
			        }
		            if (castType != null) {
			            obj.setExpression(getLanguageFactory().createFunction("cast", //$NON-NLS-1$
					            new Expression[] {obj.getExpression(),  getLanguageFactory().createLiteral(castType, TypeFacility.RUNTIME_TYPES.STRING)},
					            TypeFacility.RUNTIME_TYPES.STRING));
			        }
				} else if (obj.getExpression() instanceof ColumnReference) {
					ColumnReference elem = (ColumnReference) obj.getExpression();
					if (elem.getMetadataObject() != null) {
						String nativeType = elem.getMetadataObject().getNativeType();
						if (TypeFacility.RUNTIME_TYPES.STRING.equals(elem.getType()) && elem.getMetadataObject() != null
								&& nativeType != null && nativeType.equalsIgnoreCase(UUID_TYPE)) {
							obj.setExpression(getLanguageFactory().createFunction("cast", //$NON-NLS-1$
									new Expression[] { obj.getExpression(),
											getLanguageFactory().createLiteral("varchar", //$NON-NLS-1$
													TypeFacility.RUNTIME_TYPES.STRING) }, TypeFacility.RUNTIME_TYPES.STRING));
						}
					}
				}
                super.visit(obj);
            }
        };
    }

	public void setPostGisVersion(String postGisVersion) {
		this.postGisVersion = Version.getVersion(postGisVersion);
	}

	@TranslatorProperty(display="PostGIS Version", description="The version of the PostGIS extension.",advanced=true)
	public String getPostGisVersion() {
		return postGisVersion.toString();
	}

	@TranslatorProperty(display="Proj support enabled", description="If PostGIS Proj support is enabled for ST_TRANSFORM",advanced=true)
	public boolean isProjSupported() {
		return projSupported;
	}

	public void setProjSupported(boolean projSupported) {
		this.projSupported = projSupported;
	}

    @Override
	public MetadataProcessor<Connection> getMetadataProcessor() {
		return new JDBCMetadataProcessor() {
			@Override
			public String getRuntimeType(int type, String typeName, int precision, int scale) {
				//pg will otherwise report a 1111/other type for geometry
				if ("geometry".equalsIgnoreCase(typeName)) { //$NON-NLS-1$
					return TypeFacility.RUNTIME_NAMES.GEOMETRY;
				}
				if ("geography".equalsIgnoreCase(typeName)) { //$NON-NLS-1$
					return TypeFacility.RUNTIME_NAMES.GEOGRAPHY;
				}
				if ("json".equalsIgnoreCase(typeName) || "jsonb".equalsIgnoreCase(typeName)) { //$NON-NLS-1$ //$NON-NLS-2$
					return TypeFacility.RUNTIME_NAMES.JSON;
				}
				return super.getRuntimeType(type, typeName, precision, scale);
			}

			@Override
			protected String getGeographyMetadataTableName() {
				return "public.geometry_columns"; //$NON-NLS-1$
			}

			@Override
			protected String getGeometryMetadataTableName() {
				return "public.geography_columns"; //$NON-NLS-1$
			}
		};
	}

    @Override
    protected JDBCMetadataProcessor createMetadataProcessor() {
		JDBCMetadataProcessor metadataProcessor = new JDBCMetadataProcessor();
		metadataProcessor.setWidenUnsignedTypes(false);
		return metadataProcessor;
	}

    @Override
    public boolean preserveNullTyping() {
        return true;
    }

	@Override
	public String translateLiteralBinaryType(BinaryType obj) {
		return "E'\\\\x" + obj + '\''; //$NON-NLS-1$
	}

	@Override
	public boolean supportsLateralJoin() {
		return supportsLateralJoin && getVersion().compareTo(NINE_3) >= 0;
	}

	public ArrayEmulation supportsArrayEmulation() {
		return ArrayEmulation.NONE;
	}

	@Override
	public boolean supportsConvertFromClobToString() {
    	if (supportsConvertFromClobToString == null)
			return true;
		return super.supportsConvertFromClobToString();
    }

	@TranslatorProperty(display="Truncate long strings/CLOBs", description="Truncate strings/CLOBs, otherwise an exception will be thrown", advanced=true)
	public boolean truncateStrings() {
		return getOversizeStringProcessing().isCut();
	}

	public void setTruncateStrings(boolean truncateStrings) {
        setOversizeStringProcessing(OversizeStringMode.CUT);
	}

	@Override
	public Expression translateGeometrySelect(Expression expr) {
		return new Function(SourceSystemFunctions.ST_ASEWKB, Arrays.asList(expr), TypeFacility.RUNTIME_TYPES.VARBINARY);
	}

	@Override
	public Expression translateGeographySelect(Expression expr) {
		return new Function(SourceSystemFunctions.ST_ASEWKB, Arrays.asList(
				new Function("CAST", //$NON-NLS-1$
						Arrays.asList(expr, new Literal("geometry", TypeFacility.RUNTIME_TYPES.STRING)), //$NON-NLS-1$
						TypeFacility.RUNTIME_TYPES.GEOMETRY)),
				TypeFacility.RUNTIME_TYPES.VARBINARY);
	}

	@Override
	public Object retrieveGeometryValue(ResultSet results, int paramIndex) throws SQLException {
		//geometry strategy includes the srid
		final byte[] bytes = results.getBytes(paramIndex);
		if (bytes != null) {
			return new GeometryInputSource() {
				@Override
				public InputStream getEwkb() throws Exception {
					return new ByteArrayInputStream(bytes);
				}
			};
		}
		return null;
	}

	@Override
	public Object retrieveGeographyValue(ResultSet results, int paramIndex)
			throws SQLException {
		return retrieveGeometryValue(results, paramIndex);
        /*//geography for now only includes the wkb
        //to include the srid
        final byte[] bytes = results.getBytes(paramIndex);
        if (bytes != null) {
            return new GeographyType(bytes);
        }
        return null;*/
	}

	@Override
	public boolean supportsGeographyType() {
		return this.postGisVersion.compareTo(ONE_5) >= 0;
	}


}
