postgresql execute dynamic sql command

Question

I'm slowly learning more about PostgreSQL, as we are attempting to move to it from MSSQL Server.

In MSSQL I have the following code:

DECLARE ServiceabilityParameters 
CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL FOR
SELECT  WorkbookParameterType.ID, 
        WorkbookParameterType.Name, 
        WorkbookParameter.DefaultValue,  
        WorkbookParameter.CommandText
FROM    WorkbookParameter
JOIN    WorkbookParameterType ON WorkbookParameterType.ID = WorkbookParameter.WorkbookParameterTypeID
JOIN    WorkbookParameterDirectionType ON WorkbookParameterDirectionType.ID = WorkbookParameter.WorkbookParameterDirectionTypeID
AND                                       WorkbookParameterDirectionType.Writable = 1
WHERE   WorkbookParameter.WorkbookID = @WorkbookID

OPEN ServiceabilityParameters

FETCH NEXT FROM ServiceabilityParameters INTO @WorkbookParameterTypeID, @WorkbookParameterTypeName, @WorkbookDefaultValue, @WorkbookCommandText

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @ActualValue        NVARCHAR(256) = NULL    

IF @WorkbookCommandText IS NOT NULL
BEGIN
    EXEC sp_executesql  @statement              = @WorkbookCommandText, 
                        @params                 = N'@ApplicationContainerID INT, @Value NVARCHAR(256) OUTPUT', 
                        @ApplicationContainerID = @ApplicationContainerID,
                        @Value                  = @ActualValue OUTPUT
END

IF @ActualValue IS NULL AND @WorkbookDefaultValue IS NOT NULL
BEGIN
    SET @ActualValue = @WorkbookDefaultValue
END

INSERT  @InputParameters (
    ID, Name, Value
) VALUES (
    @WorkbookParameterTypeID, @WorkbookParameterTypeName, @ActualValue
)

FETCH NEXT FROM ServiceabilityParameters INTO @WorkbookParameterTypeID, @WorkbookParameterTypeName, @WorkbookDefaultValue, @WorkbookCommandText
END

CLOSE ServiceabilityParameters
DEALLOCATE ServiceabilityParameters

I'm trying to work out how to do the sp_executesql part in a PostgreSQL function. I believe that I can do the rest, but most of the examples that I have found show a simple select with maybe a few variables, whereas I need to execute another function, with parameters, where the function name is text in a table.

Many Thanks.

postgresql.org/docs/current/static/…

user330315
– user330315

2016-06-09 05:53:48 +00:00
Commented Jun 9, 2016 at 5:53 — user330315
– user330315, Commented Jun 9, 2016 at 5:53

XNH · Accepted Answer · 2020-07-22 10:43:24Z

3

In case you want to execute a function with parameters

EXECUTE 'SELECT Value FROM ' || v_workbookCommandText || '(ApplicationContainerID :=$1)'
INTO v_actualValue
USING v_applicationContainerID;

In case you need select records a function, you can using INOUT refcursor variable

EXECUTE 'SELECT Value FROM ' || v_workbookCommandText || '(ApplicationContainerID :=$1, refcur:= $2)'
INTO v_actualValue
USING v_applicationContainerID, my_cursor;

edited Jul 22, 2020 at 10:43

answered Jul 22, 2020 at 7:30

XNH

615 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hdiogenes · Accepted Answer · 2016-06-08 23:43:36Z

0

I think what you want to do is EXECUTE 'some string', like this:

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Another option is to create and use your own PL/PGSQL functions.

answered Jun 8, 2016 at 23:43

hdiogenes

7597 silver badges16 bronze badges

1 Comment

Boothy Over a year ago

Not really ... unless 'some string' can be the name of another function with parameters ... but cheers anyway.

Collectives™ on Stack Overflow

postgresql execute dynamic sql command

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related